SQLite in Python

SQLite is a lightweight disk-based storage that doesn’t require separate server process in python.Some application can use Sqlite for internal data storage. We can just import the sqlite3 module to create the database in our system. The data will be stored in the database_name.db file.

Create a new database in Sqlite using python

import sqlite3
conn = sqlite3.connect('employee.db')
cursor = conn.cursor()
# Employee_details table created in the database employee
cursor.execute("CREATE TABLE employee_details (Id  int primary key, Name varchar, Department varchar, Joining_Date Date)")
conn.close()

Sqlite3.connect(‘<database_name>’)

The connect() function of sqlite module is used to create the connection object to create the database. If the database is opened successfully, it returns the connection object.

connection.cursor()

Once you have create the connection, you can create the cursor object to execute the SQL commands.

cursor.execute()

The execute() function will only execute a single SQL statements on Sqlite database. If you try to execute more than one statement with it, it will raise a Warning.The sqlite3 module supports two kinds of placeholders in the execute function: question marks (qmark style) and named placeholders (named style).

connection.close()

It will close the database connection. The cursor will be unusable from this point forward

Output of this program

Inserting and fetch the data from SQLite database using python

import sqlite3
import json

#Create a database connection
conn = sqlite3.connect('employee.db')
cursor = conn.cursor()

#Create table Employee_details
cursor.execute("CREATE TABLE employee_details (Name varchar, Age int, City varchar)")

#Json String
x = '{ "name":"Peter", "age":40, "city":"Chicago"}'

#Convert from Json to Python using json.loads() method
data = json.loads(x)

#Insert the values into the employee_details table
conn.execute("INSERT INTO employee_details values(?, ?, ?)",(data["name"],data["age"], data["city"]))

#query database
rows = cursor.execute("SELECT * from employee_details WHERE Name = 'Peter'")
for row in rows:
	print(row)

conn.close()

Here we have used Sqlite3 module to create the database in our local system. The json module is used to load the json string into python variable.

Once we have established the database connection, We can execute the SQL commands such as INSERT,SELECT queries in the employee_details table.

Output

C:\revisit_class>python load_json_sqlite.py
('Peter', 40, 'Chicago')