Python SQLite

Python and SQLite

First you need to install sqlite3 module.

  • In Pycharm please go to File>Settings>Project:YourProjectName > Project interpreter :  then on the right side click on + and search sqlite3

Generally , jetbrains is very interested in complicating his menus yell.

  • In Thoony go to tools > Manage Packages… and search sqlite3

Please download SQLite from here : https://www.sqlite.org/download.html

SQLite Studio is a very useful IDE for SQLite.

 

Create a database connection to a SQLite database :

import sqlite3
from sqlite3 import Error

def create_SQLiteConnection(DbFileName):
    Connection = None
    try:
        Connection = sqlite3.connect(DbFileName)
        print(sqlite3.version)
    except Error as e:
        print("Create connection failed because : ",e)

    return Connection

Create a Table in a Database :

def create_SQLiteTable(SQLiteConnection, create_table_sql):
    try:
        c = SQLiteConnection.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print("Create Table failed because : ",e)

Insert To table :

def insert_personel(Connection, personel):
    sql = ''' INSERT INTO Personels(pname,begin_date,end_date)
              VALUES(?,?,?) '''
    cur = Connection.cursor()
    cur.execute(sql, personel)
    Connection.commit()
    return cur.lastrowid


def insert_access(Connection, acc):
    sql = ''' INSERT INTO access(acctitle,priority,status_id,personel_id,begin_date,end_date)
              VALUES(?,?,?,?,?,?) '''
    cur = Connection.cursor()
    cur.execute(sql, acc)
    Connection.commit()
    return cur.lastrowid

#TEST

database = r"accsqlite.db"

sql_create_personels_table = """ CREATE TABLE IF NOT EXISTS Personels (
                                    id integer PRIMARY KEY,
                                    pname text NOT NULL,
                                    begin_date text,
                                    end_date text
                                ); """

sql_create_accesss_table = """CREATE TABLE IF NOT EXISTS access (
                                id integer PRIMARY KEY,
                                acctitle text NOT NULL,
                                priority integer,
                                status_id integer NOT NULL,
                                personel_id integer NOT NULL,
                                begin_date text NOT NULL,
                                end_date text NOT NULL,
                                FOREIGN KEY (personel_id) REFERENCES Personels (id)
                            );"""

Connection = create_SQLiteConnection(database)

if Connection is not None:

    create_SQLiteTable(Connection, sql_create_personels_table)

    create_SQLiteTable(Connection, sql_create_accesss_table)
    personel = ('Masoud Ebrahimi', '2021-01-01', '2021-01-30');
    personel_id = insert_personel(Connection, personel)

Update a table :

def update_access(Connection, acc):
    sql = ''' UPDATE access
              SET priority = ? ,
                  begin_date = ? ,
                  end_date = ?
              WHERE id = ?'''
    cur = Connection.cursor()
    cur.execute(sql, acc)
    Connection.commit()

#TEST
update_access(Connection, (2, '2021-02-04', '2015-03-06', 2))

Select from a table :

def select_all_accesss(Connection):
    cur = Connection.cursor()
    cur.execute("SELECT * FROM access")

    rows = cur.fetchall()

    for row in rows:
        print(row)

Complete sample code :

import sqlite3
from sqlite3 import Error

def create_SQLiteConnection(DbFileName):
    SQLiteConnection = None
    try:
        SQLiteConnection = sqlite3.connect(DbFileName)
        print(sqlite3.version)
    except Error as e:
        print("Create SQLiteConnection failed because : ",e)

    return SQLiteConnection

def create_SQLiteTable(SQLiteConnection, create_table_sql):
    try:
        c = SQLiteConnection.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print("Create Table failed because : ",e)

def insert_personel(SQLiteConnection, personel):
    sql = ''' INSERT INTO Personels(pname,begin_date,end_date)
              VALUES(?,?,?) '''
    cur = SQLiteConnection.cursor()
    cur.execute(sql, personel)
    SQLiteConnection.commit()
    return cur.lastrowid


def insert_access(SQLiteConnection, acc):
    sql = ''' INSERT INTO access(acctitle,priority,status_id,personel_id,begin_date,end_date)
              VALUES(?,?,?,?,?,?) '''
    cur = SQLiteConnection.cursor()
    cur.execute(sql, acc)
    SQLiteConnection.commit()
    return cur.lastrowid

def update_access(SQLiteConnection, acc):
    sql = ''' UPDATE access
              SET priority = ? ,
                  begin_date = ? ,
                  end_date = ?
              WHERE id = ?'''
    cur = SQLiteConnection.cursor()
    cur.execute(sql, acc)
    SQLiteConnection.commit()

def select_all_accesss(SQLiteConnection):
    cur = SQLiteConnection.cursor()
    cur.execute("SELECT * FROM access")

    rows = cur.fetchall()

    for row in rows:
        print(row)


def select_access_by_priority(SQLiteConnection, priority):
    cur = SQLiteConnection.cursor()
    cur.execute("SELECT * FROM access WHERE priority=?", (priority,))

    rows = cur.fetchall()

    for row in rows:
        print(row)

def main():
    database = r"accsqlite.db"

    sql_create_personels_table = """ CREATE TABLE IF NOT EXISTS Personels (
                                        id integer PRIMARY KEY,
                                        pname text NOT NULL,
                                        begin_date text,
                                        end_date text
                                    ); """

    sql_create_accesss_table = """CREATE TABLE IF NOT EXISTS access (
                                    id integer PRIMARY KEY,
                                    acctitle text NOT NULL,
                                    priority integer,
                                    status_id integer NOT NULL,
                                    personel_id integer NOT NULL,
                                    begin_date text NOT NULL,
                                    end_date text NOT NULL,
                                    FOREIGN KEY (personel_id) REFERENCES Personels (id)
                                );"""

    # create a database SQLiteConnection
    SQLiteConnection = create_SQLiteConnection(database)

    # create tables
    if SQLiteConnection is not None:
        # create Personels table
        create_SQLiteTable(SQLiteConnection, sql_create_personels_table)

        # create access table
        create_SQLiteTable(SQLiteConnection, sql_create_accesss_table)

        personel = ('Masoud Ebrahimi', '2021-01-01', '2021-01-30');
        personel_id = insert_personel(SQLiteConnection, personel)

        # access
        access_1 = ('Access to Main door from main controller', 1, 1, personel_id, '2015-01-01', '2015-01-02')
        access_2 = ('Access to the North gate', 1, 1, personel_id, '2015-01-03', '2015-01-05')

        # create access
        insert_access(SQLiteConnection, access_1)
        insert_access(SQLiteConnection, access_2)

        update_access(SQLiteConnection, (2, '2021-02-04', '2015-03-06', 2))

        print("1. Query acc by priority:")
        select_access_by_priority(SQLiteConnection, 1)

        print("2. Query all access")
        select_all_accesss(SQLiteConnection)




    else:
        print("Error! cannot create the database SQLiteConnection.")




if __name__ == '__main__':
    main()


Share to :

Leave a Reply

Your email address will not be published. Required fields are marked *