SQLite3 Python

Introduction

SQLite is a compact embedded database with open source.

In this article, you will learn how to work with SQLite3 in Python 3.

Import from .csv file

Let's say you have a file. data.csv The values are separated by a semicolon.

Import the contents of this file into the SQLite3 database.

You can find in this article several ways to make such an import.

The simplest example.

Create a file. from_csv.py And copy the code from the examples.

You can start the file command python3 from_csv.py

import csv, sqlite3 con = sqlite3.connect('db/my.db') cur = con.cursor() cur.execute("CREATE TABLE t (picture, price, number);") # picture, price, number are column names with open('data.csv','r') as fin: # by default csv.DictReader uses the first row as column headers dr = csv.DictReader(fin, delimiter=";") to_db = [(i['picture'], i['price'], i['number']) for i in dr] cur.executemany("INSERT INTO t (picture, price, number) VALUES (?, ?, ?);", to_db) con.commit() con.close()

python3 from_csv.py

A file should appear in the db directory my.db

This database should have a table. t repeating the content of your .csv file

A more realistic example

Now let’s look at a more realistic example.

The table should have an id column. It is advisable to generate it automatically.

It is also easier to read the code with the help of docstring

Let's look at the file. partners.csv listing

name;url;area URN.SU;https://urn.su;IT HeiHei.ru;https://heihei.ru;Travel TopBicycle.ru;https://topbicycle.ru;Bicycles Aviasales;https://aviasales.ru;Travel Booking.com;https://booking.com;Hotels Hotellook;https://Hotellook.com;Hotels Velodrive;https://velodrive.ru;Bicycles Xiaomi;https://mi-shop.com;Android Samsung;https://www.samsungstore.ru;Android GeekBrains;https://gb.ru;Education Netology;https://netology.ru;Education TestSetup.ru;https://TestSetup.ru.ru;Education Pluralsight;https://Pluralsight.com;Education SovcomInsurance;https://sovcomins.ru;Insurance Polis 812;https://polis812.ru;Insurance Vivo;https://ru.vivo.com/;Android Beget;https://beget.com;Hosting Reg.ru;https://Reg.ru.ru;Hosting OLDI;https://oldi.ru;Laptops

The file has three values per line: name, url, area. Add these columns to the database.

In the names there are Cyrillic symbols, so when opening, specify encoding="utf8"

Also, do not forget to generate id.

A new, more real-life file friends.py It will look like this:

import csv, sqlite3 con = sqlite3.connect('db/partners.db') cur = con.cursor() cur.execute("""CREATE TABLE friends ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, url TEXT, area TEXT )""") with open('partners.csv','r', encoding="utf8") as f: dr = csv.DictReader(f, delimiter=";") to_db = [(i['name'], i['url'], i['area']) for i in dr] cur.executemany("INSERT INTO friends (name, url, area) VALUES (?, ?, ?);", to_db) con.commit() con.close()

python3 friends.py

List all tables and their columns

A list of all tables in the database:

import sqlite3 def print_tables(db) -> None: try: con = sqlite3.connect(db) cur = con.cursor() cur.execute("SELECT name FROM sqlite_master WHERE type='table';") print(cur.fetchall()) except Exception as e: print(f"print_tables() " f"Connection to {db} failed:\n{e}") if __name__ == "__main__": print_tables("my.db")

[('table_name8',), ('sqlite_sequence',)]

The following script gets every table of the database and the names of its columns.

# Importing Sqlite3 Module import sqlite3 try: # Making a connection between sqlite3 # database and Python Program sqliteConnection = sqlite3.connect('testers.db') # If sqlite3 makes a connection with python # program then it will print "Connected to SQLite" # Otherwise it will show errors print("Connected to testers.db") # Getting all tables from sqlite_master sql_query = """SELECT name FROM sqlite_master WHERE type='table';""" # Creating cursor object using connection object cursor = sqliteConnection.cursor() # executing our sql query cursor.execute(sql_query) print("List of tables\n") tables = cursor.fetchall() # printing all tables list # print(tables) # [('all_testers',), ('tools',)] for table in tables: # print(table) table_name = table[0] print(f"\nTable: {table_name}\n") sql_query = f"SELECT * from {table_name}" cursor = sqliteConnection.execute(sql_query) names = list(map(lambda x: x[0], cursor.description)) for name in names: print(f"column: {name}") except sqlite3.Error as error: print("Failed to execute the above query", error) finally: # Inside Finally Block, If connection is # open, we need to close it if sqliteConnection: # using close() method, we will close # the connection sqliteConnection.close() # After closing connection object, we # will print "the sqlite connection is # closed" print("the sqlite connection is closed")

If the database testers.db has two tables, all_testers and tools, the output looks like this:

Connected to testers.db List of tables Table: all_testers column: tester_id column: name column: tool Table: tools column: tool_id column: name column: users column: rating the sqlite connection is closed

Article author: Andrei Olegovich