Python Sqlite - How do i use Sqlite in Python

In 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

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

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.

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

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