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 
.
- 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()