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
Step 1: First of all, we need to import the sqlite3 module in our python code.
|
import sqlite3 |
Step 2: Using the connect () method connect your python to the SQLite database
|
# Connect to sqlite database con = sqlite3.connect('studentDb.db') |
Step 3: You have now connected to the SQLite database, using the cursor () to return your cursor object.
|
# Cursor object cur = con.cursor() |
Step 4: You can now run any SQL query using the execute() method. For instance, let's select data from the Studentsmarks table in the database.
|
# Execute SQL query cur.execute("Select * from Studentsmarks") |
Step 5: lastly, close your database connection using the con.close() method.
|
# 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
|
Article Contributed By :
|
|
|
|
|
893 Views |
|