How to create the new database in Sqlite using Python
Contents
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
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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
|
1 2 |
C:\revisit_class>python load_json_sqlite.py ('Peter', 40, 'Chicago') |
