Python Sqlite - How do i use Sqlite in Python
Last updated Apr 27, 2021In this python tutorial we will learn how to connect SQLite Database in python, SQLite connection in python. SQLite is a server less database which will be used in all other programming languages including in python also. SQLite is a relational database system which can easily connect to application without any configuration procedure. In this tutorial we will connect python application with SQlite3 database.
Create SQLite Connection
To work with SQLite database first we need to import sqlite3 module and we need to create connection to database by executing the SQLite statement of sqlite.connect() method.
import sqlite3
con = sqlite3.connect('sqliteDB.db')
|
To execute sqlite statements we need to create cursor object. To create cursor object we will use the sqlite connection cursor() method
con = sqlite3.connect('sqliteDB.db')
cursorObj = con.cursor()
|
Establish SQLite Database Connection
To establish sqlite connection import sqlite3 module then create connection by con.connect(). To handle connection errors and other exceptions we will use try, catch blocks. After executing our sqlite statements we will close the connection by calling close() method.
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect(':memory:')
print("Connection is established: Database is created in memory")
except Error:
print(Error)
finally:
con.close()
sql_connection()
|
Output
E:\IDE\pythonProject1\venv\Scripts\python.exe E:/IDE/pythonProject1/main.py |
Create Database tables in Python SQLite
To create tables we need to execute create table sql statement
In this example we are creating a Player table which has fields id, name, runs and country fields
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect('sqliteDB.db')
return con
except Error:
print(Error)
def sql_table(con):
cursorObj = con.cursor()
cursorObj.execute("CREATE TABLE Player(id integer PRIMARY KEY, name text, runs text, country text)")
con.commit()
con = sql_connection()
sql_table(con)
|
In this above code we define two methods sql_connection() and sql_table(con) which are used to create a sqlite database connection and other is for create student table. After executing the create statement we saved the statements by calling the commit() method, which will save all changes we have done.
To check the created database we can use SQLiteBrowser to view the database details, tables etc
Insert data into python sqlite table
To insert data into created table we need to execute insert query statement
cursorObj.execute("INSERT INTO Player VALUES(1, 'Sachin', 13000, 'India')")
con.commit()
|
In the above sql statement we inserted player data as direct values. we can also pass the values as entries.
Insert data into table using entries
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect('sqliteDB.db')
return con
except Error:
print(Error)
def sql_insert(con, entities):
cursorObj = con.cursor()
cursorObj.execute(
'INSERT INTO Player(id, name, runs, country) VALUES(?, ?, ?, ?)',
entities)
con.commit()
entities = (2, 'Ponting', 12000, 'Australia')
con = sql_connection()
sql_insert(con, entities)
|
Python Sqlite Update Query
def sql_update(con):
cursorObj = con.cursor()
cursorObj.execute('UPDATE Player SET name = "Ricky Ponting" where id = 2')
con.commit()
|
Python Sqlite Select Statement
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect('sqliteDB.db')
return con
except Error:
print(Error)
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('SELECT * FROM Player')
rows = cursorObj.fetchall()
for row in rows:
print(row)
con = sql_connection()
sql_fetch(con)
|
Output: The above select statement will print the below output data
(1, 'Sachin', '13000', 'India') |
SQLite3 Other Query methods
rowcount will return the number of rows effected while executing the sql statement
example
cursorObj.execute('DELETE FROM employees').rowcount
|
Will return the number of rows deleted while executing the above statement
to fetch all the rows from the select statement we will use the fetchall() method on cursor object.
cursorObj.fetchall() |
How to check the table exist or not in sqlite database
to create a table if table not exist we will use the below query statement
cursorObj.execute('create table if not exists Player(pass your required fields)')
|
Drop table
we can delete/drop table by using the drop query statement
drop table if exists table_name
|
Execute bulk insert statements in python
we can insert multiple data in a single query statement by using executemany() method
def sql_bulInsertion(con):
cursorObj = con.cursor()
data = [(3, 'Sehwag', 11000, 'India'), (4, 'Lara', 10000, 'Westindies'), (5, 'Jayasurya', 12000, 'srilanka')]
cursorObj.executemany("INSERT INTO Player VALUES(?, ?,?,?)", data)
sql_bulInsertion(con)
|
After executing the bulk data select statement will return the below data
(1, 'Sachin', '13000', 'India') |
SQLite method
- sqlite3.connect(database [optional arguments])
- connection.cursor([cursorObject])
- cursor.execute(sql [, optional parameters])
- cursor.executemany(sql, seq_of_parameters)
- cursor.executescript(sql_script)
- connection.total_changes()
- connection.commit()
- connection.rollback()
- connection.close()
- cursor.fetchone()
- cursor.fetchmany([size = cursor.arraysize])
- cursor.fetchall()
Conclusion: In this python sqlite example we cover How do i use Sqlite3 in python, how to connect sqlite database, create tables and CRUD operations.
Related:
Python remove last character from string
Python JSON - Read JSON file Python
Article Contributed By :
|
|
|
|
1238 Views |