Hello friends how are you, Today in this post " SQLite in Python " i am going to teach how you can use SQLite in Python and if you want to create projects in python using SQLite database then this post will help you definitely.
If you want to understand this through video then watch this video i have explained it step by step live
What is SQLite?
1.SQLite is a library which is used as a internal data storage.2.SQLite3 module is written by Gerhard Haring.
3.It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249.
4.If you want to use SQLite in Python then you do not need to install this library separately, you can connect directly with database.
Connection with Database
To create a connection with SQLite first we have to import a module sqlite3 in our code then we can create a connection object to connect. A predefined function connect() of module sqlite3 is used to create a connection object. Following is the code to connect with SQLite database.
import sqlite3
conn=sqlite3.connect("student.db")
print("Database created successfully")
Here we can type any name for database for example i have created a student database . After typing the database name don't forget to add .db because it is the extension of database file. Here the database file with name student will be created inside the current directory but you can change your file path if needed. when you will run this code you will get a message like Database created successfully. Below is the screenshot of project folder.
In the above screenshot you can see that a database file with name student is created.
Create Table
After creating database we need to create a table inside database. Here i am going to create a STUDENT REGISTRATION table with following fields[STUD_ID,STU_NAME,STU_CONTACT,STU_EMAIL,STU_ROLLNO,STU_BRANCH]. Here is the complete code to create this table in the database.
import sqlite3
conn=sqlite3.connect("student.db")
print("Database Opened successfully")
conn.execute("""
CREATE TABLE STUD_REGISTRATION(
STU_ID INTEGER PRIMARY KEY NOT NULL ,
STU_NAME TEXT NOT NULL,
STU_CONTACT TEXT,
STU_EMAIL TEXT,
STU_ROLLNO TEXT NOT NULL,
STU_BRANCH TEXT NOT NULL)
""")
print ("Table STUD_REGISTRATION created successfully")
"""
###Output###
Database Opened successfully
Table STUD_REGISTRATION created successfully
"""
Here execute() is a predefined function which is used to executer any query of SQLite. If you want to see your table in SQLite then you can use DB Browser.
Insert data into table
Now i am going to insert data into table. By using SQL query we can insert data into tables. Following is the syntax to insert data into table.
INSERT INTO TABLE_NAME (column1, column2,column3,...columnN)
VALUES (value1, value2, value3,...valueN);
By using the above syntax here is the query to insert data into table .
import sqlite3 conn=sqlite3.connect("student.db") print("Database Opened successfully") conn.execute("INSERT INTO STUD_REGISTRATION (STU_ID,STU_NAME,STU_CONTACT,STU_EMAIL,STU_ROLLNO,STU_BRANCH) \ VALUES (1,'Raju', '9179876567', 'raju@gmail.com','MCA204', 'CA')"); conn.execute("INSERT INTO STUD_REGISTRATION (STU_ID,STU_NAME,STU_CONTACT,STU_EMAIL,STU_ROLLNO,STU_BRANCH) \ VALUES (2,'Nancy', '9179785695', 'nancy@gmail.com','MCA225', 'CA')"); conn.commit() print ("Records inserted successfully") conn.close() """ ###Output### Database Opened successfully Records inserted successfully """
Now its time to see the data stored in Database table. Following is the program to fetch data from table
import sqlite3
conn = sqlite3.connect('student.db')
print ("Database opened successfully")
cursor = conn.execute("SELECT * from STUD_REGISTRATION")
print("NAME\tCONTACT\t\tEMAIL\t\t\tROLLLNO\tBRANCH")
for row in cursor:
print ("{}\t{}\t{}\t{}\t{}".format(row[1],row[2],row[3],row[4],row[5]))
conn.close()
When you will execute this program then you will get output like below screenshot
Here you can verify that these are the records which are inserted using query.
Update data into table
In most of the cases we have to update data into table for example sometime by mistake we insert wrong data into table then it is necessary to update the wrong data with correct. Suppose that in the above records we have to update the ROLLNO of Nancy. Currently it is MCA225 and we have to update it with MCA325. To do this see the following program
#import library
import sqlite3
#open databse
conn = sqlite3.connect('student.db')
print ("Database opened successfully")
#update record
conn.execute("UPDATE STUD_REGISTRATION set STU_ROLLNO = 'MCA325' where STU_ID = 2")
conn.commit()
#display recrod
cursor = conn.execute("SELECT * from STUD_REGISTRATION")
print("NAME\tCONTACT\t\tEMAIL\t\t\tROLLLNO\tBRANCH")
for row in cursor:
print ("{}\t{}\t{}\t{}\t{}".format(row[1],row[2],row[3],row[4],row[5]))
conn.close()
When you will execute the above code you will get the following result. Focus on ROLLNO of Nancy to see the changes.
Delete data from table
Sometime we have to delete the complete record of a Person , Product, Company etc. Suppose that we have to delete the record of Nancy from our database then the following code will use.
#import library
import sqlite3
#open databse
conn = sqlite3.connect('student.db')
print ("Database opened successfully")
#delete record
conn.execute("DELETE from STUD_REGISTRATION where STU_ID = 2")
conn.commit()
#display recrod
cursor = conn.execute("SELECT * from STUD_REGISTRATION")
print("NAME\tCONTACT\t\tEMAIL\t\t\tROLLLNO\tBRANCH")
for row in cursor:
print ("{}\t{}\t{}\t{}\t{}".format(row[1],row[2],row[3],row[4],row[5]))
conn.close()
The above program will delete the Nancy record and display the remaining record. For better understanding see the output of the above program.
Here in the above output screenshot you will notice that there is only record in database and Nancy's record is deleted successfully.
Request:-If you found this post helpful then let me know by your comment and share it with your friend.
If you want to ask a question or want to suggest then type your question or suggestion in comment box so that we could do something new for you all.
If you have not subscribed my website then please subscribe my website. Try to learn something new and teach something new to other.
Thanks.😊
0 Comments