If you would like to become a Python backend programmer, you'll need to learn how to connect Python to an SQLite database and execute standard CRUD tasks. By the end of this, the post you will have learned:
-
How to Create a Python SQLite Connection
-
How to Create an SQLite Database Table
-
How to perform ou CRUD operations
-
How to Connect Python with SQLite Database
|
# Connect to sqlite database
con = sqlite3.connect('studentDb.db')
|
|
# Cursor object
cur = con.cursor()
|
|
# Execute SQL query
cur.execute("Select * from Studentsmarks")
|
|
# Close connection
con.close()
|
-
How do i create new table in SQLite Database
SQLite databases use tables to store the user data, thus you need to create tables to store and retrieve your information. In the steps below post, we are going to create an SQLite table "Studentsmarks” with unit_ID as the primary key.
Step 1: First of all, establish a database connection
|
# Connect to sqlite database
con = sqlite3.connect('studentDb.db')
try:
Step 2: Call your cursor object
# Cursor object
cur = con.cursor()
|
Step 3: Now, run the table creation query
|
# Execute query
cur.execute('''CREATE TABLE Studentsmarks (
unit_Id INTEGER PRIMARY KEY AUTOINCREMENT,
UnitName TEXT (500) NOT NULL,
Student TEXT (252) NOT NULL,
Age INTEGER,
Country TEXT (252) NOT NULL);''')
# Commit changes
con.commit()
# Print successful message
print(' Studentsmarks table created successfully')
except:
print('Error occurred...')
# Roll back if in case of issue
con.rollback()
Step 4: Close connection
con.close()
|
-
SQLite CRUD operations
The SQLite CRUD operations include four important operations:
-
Create
-
Retrieve
-
Update
-
Delete
Create
This operation involves inserting a new row in a SQlite database table. To create a new row, follow the following simple guidelines:
|
import sqlite3
db=sqlite3.connect('studentdb')
qry="insert into Studentsmarks (name, age, marks) values('Ranjha', 20, 50);"
try:
cur=db.cursor()
cur.execute(qry)
db.commit()
print ("Marks added successfully")
except:
print ("error in operation")
db.rollback()
db.close()
|
Retrieve
This operation involves retrieving a particular row or the entire database table information using the fetchone () and fetchall () methods respectively.
To fetch a single query:
|
import sqlite3
db=sqlite3.connect('studentdb')
sql="SELECT * from Studentsmarks;"
cur=db.cursor()
cur.execute(sql)
while True:
record=cur.fetchone()
if record==None:
break
print (record)
db.close()
|
To fetch all database queries:
Update
This operation involves altering the information in the database table. Using this operation you can be able to update any row or the entire table information. This operation works with conditions. For example, in the following code, we are going to update the query where
|
import sqlite3
db=sqlite3.connect('studentdb')
qry="update Studentsmarks set age=? where name=?;"
try:
cur=db.cursor()
cur.execute(qry, (19,'Shamir'))
db.commit()
print("record updated successfully")
except:
print("error in operation")
db.rollback()
db.close()
|
Delete
This operation allows you to drop a particular entry in the database table. You can drop a particular row or drop the entire table. For instance, in the following code, we are going to drop the query where
|
import sqlite3
db=sqlite3.connect('test.db')
qry="DELETE from student where name=?;"
try:
cur=db.cursor()
cur.execute(qry, ('Bill',))
db.commit()
print("student deleted successfully")
except:
print("error in operation")
db.rollback()
db.close()
|
Conclusion
In this post, we have learned how to connect the python program to an SQLite database, create a table and finally perform various CRUD operations. These operations are very important and are common when you start your backend software development practices
