SQLite3 Cheatsheet (with Python)

**SQLite data types:

INTEGER (int), REAL (float), TEXT (str), BLOB (bytes), NULL (None)**

Connect (or create) to a database

conn = sqlite3.connect('C://User/Directory/database.db')

In case the command receives the address of a nonexistent database, it will create a new one with said address.

Close database

conn.close()

Create a cursor and close it

cur = conn.cursor()

cur.close()

Commit changes

conn.commit() # commit changes

The commit method must be called with the connection function

Create a table

cur.execute('''CREATE TABLE IF NOT EXISTS taylor_swift
                (album TEXT, year INT)''')
conn.commit() # commit changes

Here we use an cursor to execute the query, but the execute method can be called directly with the connection (‘‘‘conn.execute()’’’)

Inserting a list into the database

#using tuples and lists
cur.executemany(
    '''INSERT INTO taylor_swift (album, year) VALUES (?, ?)''',list_name
)

#using dictionaries
curs.execute(
    """INSERT INTO taylor_swift VALUES (:album, :year)""", {'album': album, 'year': year})

conn.commit() # commit changes

In order of this method to work, the second argument must me an iterable, in this case, the list must be a list of tuples; using placeholders (?, ?) is useful to prevent SQL injection attacks

Running a SQL script from a file

with open('D:\\script.sql') as file:
    sql_script = file.read()

cur.executescript(sql_script)

If there’s a commit command on the script text, there’s no need to call the function again

fetchone()

cur.execute("SELECT album FROM taylor_swift" WHERE year=2022)

cur.fetchone()

this command will fetch the next line of the query and in the absence of a line it will just return None.

fetchmany()

cur.execute("SELECT album FROM taylor_swift WHERE year>?", ('2012',))

cur.fetchmany(5)

this command will return an specific number of rows from the query as a list, or None if there’s not enough rows.

fetchall()

cur.execute("SELECT * FROM taylor_swift WHERE album=:album", {'album':'Fearless'})

cur.fetchall()

will return all rows from the query as a list

.connect(:memory:)

conn.connect(:memory:)

will allocate a space in memory for a temporary database, that can be saved in a file afterwards, which is useful for testing. the database ceases to exists as soon as the connection is closed. every run of the code starts completely fresh, so running the same INSERTs won’t cause errors.

function examples

considering a class emp that takes {first_name, last_name, pay}, a couple functions using sqlite3 are:

def insert_employee(emp):
    with conn:
        curs.execute(
              """INSERT INTO employees VALUES (:first_name, :last_name, :pay)""", 
            {'first_name': emp.first_name, 'last_name': emp.last_name, 'pay': emp.pay})


def get_employee_by_name(last_name):
        curs.execute("""SELECT * FROM employees WHERE last_name=:last_name""", 
                     {'last_name': last_name})
        return curs.fetchall()


def update_pay(emp, pay):
    with conn:
        curs.execute("""UPDATE employees SET pay=:pay
                     WHERE first_name=:first_name AND last_name=:last_name""",
                     {'pay': pay, 'first_name': emp.first_name, 'last_name':emp.last_name})


def delete_employee(emp):
    with conn:
        curs.execute("""DELETE FROM employees WHERE first_name=:first_name AND last_name=:last_name""", 
                     {'first_name': emp.first_name, 'last_name': emp.last_name})

the with conn: is added so that a conn.commit() isn’t needed after every function call.