sqlite

sqlite#

sqlite3 package implements a sqlite database in the python. It’s usually build in package. Check its page in python.org.

import sqlite3
from pathlib import Path
from src.sqlite import execute_several_statements

Usage#

To use database you need: create connection, get cursor, manipulate with database using cursor methods.

The following cell creates the connection - it passes :memory: to use the database only in memory without any file where data will be stored.

con = sqlite3.connect(':memory:')

Next code shows how to create cursor and load data from it.

cursor = con.cursor()
cursor.execute("SELECT 1 as val1, 2 as val2;")

<sqlite3.Cursor at 0x73c3b067f0c0>

The following cells show how you can extract data and ged description to it.

cursor.fetchall()

[(1, 2)]

cursor.description

(('val1', None, None, None, None, None, None),
 ('val2', None, None, None, None, None, None))

Not query#

Consider how sqlite deals with commands that aren’t supposed to return data. After executing such commands:

  • fetchone: cursor returns None.

  • fetchall: cursor returns [].

  • description: attribute of the cursor takes the value None.

The following cell executes a command that creates a table and displays values taken from various different attributes and methods of the cursor.

con = sqlite3.connect(":memory:")
cursor = con.cursor()

cursor.execute("CREATE TABLE my_table (col1 INT);")

print("description", cursor.description)
print("fetchone()", cursor.fetchone())
print("fetchall()", cursor.fetchall())

cursor.close()

description None
fetchone() None
fetchall() []

Several commands#

SQLite execute doesn’t allow you to pass SQL code that contains multiple SQL statements. To execute multiple statements, you are supposed to use the curosr.executescript method. Note that the executescript method doesn’t allow you to extract data that was a result of the query.

The following cell shows a type of error you’ll get if you try to execute SQL code that contains multiple queries in one execution.

con = sqlite3.connect(":memory:")
cursor = con.cursor()
try:
    cursor.execute("""
    CREATE TABLE tab1 (val INT);
    INSERT INTO tab1 (val) VALUES (10), (40);
    """)
except Exception as e: print(type(e), e)

cursor.close()
con.close()

<class 'sqlite3.ProgrammingError'> You can only execute one statement at a time.

Execute script#

The following cells show how you can use the cursor.executescript method to execute SQL code.

con = sqlite3.connect(":memory:")
cursor = con.cursor()
cursor.executescript("""
    CREATE TABLE tab1 (val INT);
    INSERT INTO tab1 (val) VALUES (10), (40);
""")

<sqlite3.Cursor at 0x7df9101ca0c0>

The following code shows that tab1 is created and contains the appropriate data.

cursor.execute("SELECT * FROM tab1").fetchall()

[(10,), (40,)]

But the same query in the executescript method produces a case where the cursor doesn’t contain any data.

cursor.executescript("SELECT * FROM tab1").fetchall()

[]

cursor.close()
con.close()

DIY solution#

The cursor.execute method doesn’t allow to get the results of the queries. In order to overcome the limitation of sqlite3 this site uses DIY function src.sqlite.execute_several_statements.

The follwing code shows a solution that you can use - use execute_several_statements to execute code.

queries = """
    CREATE TABLE tab1 (val INT);
    SELECT 10 as value;
    INSERT INTO tab1 (val) VALUES (10), (40);
"""
execute_several_statements(
    cursor=cursor, queries=queries
)

[(None, []),
 ((('value', None, None, None, None, None, None),), [(10,)]),
 (None, [])]

The result is a list containing description and fetchall() of the corresponding query.

Commint#

When you insert some data into the database - using INSERT command. You must commit changes to the file system using the connection.commit method, otherwise any connection corresponding to that file won’t see any changes.

The following cell creates a connection that createa a table and inserts some data into it.

db_file = Path("/tmp/my_file.sql")
db_file.unlink(missing_ok=True)

main_con = sqlite3.connect(db_file)
cursor = main_con.cursor()

queries = """
CREATE TABLE tab1 (val INT);
INSERT INTO tab1 (val) VALUES (30), (37);
"""
ans = execute_several_statements(cursor=cursor, queries=queries)

The following cell shows that you can easily access changes from the same cursor.

cursor.execute("SELECT * FROM tab1;")
print(cursor.fetchall())
cursor.close()

[(30,), (37,)]

Even other cursor from the same connection works fine.

cursor = main_con.cursor()
print(cursor.execute("SELECT * FROM tab1;").fetchall())
cursor.close()

[(30,), (37,)]

But if you try to create a new connection and load data from it - you got noting data not commited to the file.

new_con = sqlite3.connect("/tmp/my_file.sql")
print(new_con.execute("SELECT * FROM tab1;").fetchall())

[]

The following cell commits changes of the main_con connection.

main_con.commit()

After committing, the data appeared in the new_con.

print(new_con.execute("SELECT * FROM tab1;").fetchall())
new_con.close()
main_con.close()

[(30,), (37,)]
Contents