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.
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:
## Creating cursor object and namimg it as cur cur = con.cursor() cur.execute('SELECT * from countries')
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.
## Cursor is used as an object to call fetchone() function ## Results are printed using the print function print(cur.fetchone())
Let’s try the same with the fetchall() function
## Cursor is used as an object to call fetchall() function ## Results are printed using the print function print(cur.fetchall())
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.
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:
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 = ('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:
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
cur.execute("INSERT INTO consumers VALUES (1, 'John >> Doe' , ' email@example.com' , 'A')") for row in cur.execute('SELECT * FROM consumers'): print(row)
Yay, We have successfully inserted a record into the empty consumer’s table.
insert more than one record
# Prepare a list of records to be inserted purchases = [(2 , 'John >> Paul' , 'firstname.lastname@example.org' , 'B') , (3 , 'Chris Paul' , 'email@example.com' , '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.
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()
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.
This is a guide to Python SQLite. Here we discuss Introduction to Python SQLite, Syntax, Examples to implement it with codes and outputs. You can also go through our other related articles to learn more –