In above code we used sqlite3 library to connect and manage SQLite database. We can also use SQLAlchemy to manage SQLite. The script part remain same and only the connection string changes. Here we have created the connection engine ( the variable ) my_conn. We used a common varaible my_conn as output of both connection engines so the script part remain same throughout.
from sqlalchemy import create_engine
#my_conn = create_engine("sqlite:///my_db.db")
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
In windows system, the absolute path is used in above code.
Advantage of SQLalchemy
If you want to change to other database ( say MySQL ) then only connection string needs to be changed as by using SQLAlchemy can be used to connect different databases. Here the script part mostly remain same. Easy portability is the main advantages here.
There is a small change at Parameterized query where for the placeholders we have to use %s for MySQL and ? for SQLite.
Creating table
Here is out student table, we used try except to handle any error during the table creation process.
Using SQLAlchemy
Using sqlite3
try:
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key,
name text,
class text,
mark integer,
gender text
);''')
my_conn.commit()
print("Student Table created successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
Output
Student Table created successfully
Listing tables in the database
r_set=my_conn.execute('''select name from sqlite_master
where type = 'table' ''')
for row in r_set:
print(row)
Output ( as we have already created on table in above script, we will get this output )
('student',)
Adding records to student table ( with create table )
from sqlalchemy import create_engine
#my_conn = create_engine("sqlite:///my_db.db")
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key,
name text,
class text,
mark integer,
gender text,
photo blob
);''')
Delete > Create > Add records to student table
Delete ( manually the file ) the database and then run this code. Note that the path in line 3 needs to be changed to match your system. This code will create the database my_db.db inside the sqlite directory. This will print the success message in your console.