Sqlite Doesn't Bite



How To Use Sqlite with Python

Import the requirements and connect to a database called data.db in the same directory as the script.

import sqlite3
from sqlite3 import Error

def db_connect():
    try:
        conn = sqlite3.connect('data.db')
        return conn
    except Error as e:
        print(e)

conn = db_connect()

Create a table called testing with auto-incrementing ID along with name and email columns.

def create_table(conn):
    create_table = """CREATE TABLE IF NOT EXISTS testing (
                                        ID INTEGER PRIMARY KEY AUTOINCREMENT,
                                        name TEXT NOT NULL,
                                        email TEXT NOT NULL
                                        );"""
    conn.execute(create_table)

create_table(conn)

Insert row into table if not already present.

def insert_row(conn, name, email):
    cur = conn.cursor()
    cur.execute("SELECT * FROM testing WHERE name = ?", (name,))
    if not len(cur.fetchall()):
        conn.execute(
            "INSERT INTO testing (name, email) VALUES (?, ?);", (name, email))
        conn.commit()
    else:
        print('Already exists in db')

insert_row(conn, 'impshum', 'test@test.com')

Read all from database.

def read_db(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM testing")
    rows = cur.fetchall()
    for row in rows:
        print(row)

read_db(conn)

Search database.

def search_db(conn, column, query):
    cur = conn.cursor()
    cur.execute("SELECT * FROM testing WHERE ? = ?", (column, query))
    rows = cur.fetchall()
    for row in rows:
        print(row)

search_db(conn, 'name', 'impshum')

And tying it all together in one main function, as an example, to use all the functions above.

def main():
    conn = db_connect()
    if conn:
        with conn:
            create_table(conn)
            insert_row(conn, 'impshum', 'test@test.com')
            read_db(conn)
            search_db(conn, 'name', 'impshum')

if __name__ == '__main__':
    main()

Thanks for reading. x

Resources