Updated April 14, 2023
Introduction to Python SQLite
Python SQLite can be defined as a C Library that provides the capability of a light-weight disc based database. This does not demand for any extra or any other separate server process.
We can also prototype an application with Python SQLite’s help and then eventually port the core to an extensible database like Oracle, Teradata, etc. Whereas some of the applications also uses Python SQLite for the internal data storage requirements as well.
It is basically a light-weight compact database capable of handling RDBMS small datasets.
To use the Python SQLite module, you must create a connection request using the connect() function along with the path of the directory where to connect to.
Syntax:
Python program to demonstrate the usage of Python SQLite methods
import sqlite3
con = sqlite3.connect('EDUCBA.db')After having a successful connection with the database, all you need to do is create a cursor() object & call its execute() method to execute the SQL Queries.
Examples to Implement Python SQLite
Below are the examples mentioned:
Example #1
Code:
## Creating cursor object and namimg it as cur
cur = con.cursor()
cur.execute('SELECT * from countries')Output:
Example #2
Now, if you want to fetch the results of the Select * statement that you have just run above then, you can use either the fetchone() method to showcase only a single row or otherwise, fetchall() function to display all of the rows in the form of a python list.
Code:
## Cursor is used as an object to call fetchone() function
## Results are printed using the print function
print(cur.fetchone())Output:
Example #3
Let’s try the same with the fetchall() function
Code:
## Cursor is used as an object to call fetchall() function
## Results are printed using the print function
print(cur.fetchall())Output:
Example #4
The output of the fetchall() function can be used in a different format to improve the readability of the returned records. Let us take an example of how it’s done.
Code:
for row in cur.execute('SELECT * FROM countries'):
print(row)Here we are iterating row by row using the for loop, so the output will look like this:
Output:
Example #5
What if we want to retrieve only some specific records based on some conditional. Yes, we can certainly use the where clause to have this conditional in place. Let’s take an example to understand the same:
Code:
code = ('ZWE',)
cur.execute('SELECT * FROM countries WHERE code = ?', code)
print(cur.fetchone())This Python program will return only those records which match the conditional as per the where clause. The output will be:
Output:
Example #6
Even if we want to insert new records in the table, we wish to; The same can be taken care of using the Insert statement.
One by one
Code:
cur.execute("INSERT INTO consumers VALUES (1, 'John >> Doe' , ' [email protected]' , 'A')")
for row in cur.execute('SELECT * FROM consumers'):
print(row)Output:
Yay, We have successfully inserted a record into the empty consumer’s table.
Example #7
Insert more than one record
Code:
# Prepare a list of records to be inserted
purchases = [(2 , 'John >> Paul' , '[email protected]' , 'B') ,
(3 , 'Chris Paul' , '[email protected]' , 'A'), ]
# Use executemany() to insert multiple records at a time
cur.executemany('INSERT INTO consumers VALUES (?,?,?,?)', purchases)
for row in cur.execute('SELECT * FROM consumers'):
print(row)Output: You can commit/save this by merely calling the commit() method of the Connection object you created.
conn.commit()You should be able to see the entries now –
But before that, the changes need to be made permanent, and it is achieved using the commit() method as shown above.
# Closing the DB connection
conn.close()Conclusion
If you hold expertise with SQL and you want to utilize the same within Python. Then the same can be integrated within Python. Before that, you need to connect to the database utilizing the connect() function available in SQLite3.A variety of RDBMS exists in the market like IBM DB2, My SQL, etc. This kind of RDBMS can be termed as SQLite, and these are quite famous due to many reasons.
- Lightweight & Fully transactional
- Serverless and not so complex setup
However, Everything comes up with some limitations & so does Python SQLite.
Such as it does not support some specific kind of joins, namely fully outer / Right Join.
Recommended Articles
We hope that this EDUCBA information on “Python SQLite” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
