SQLite3 API's

SQLite3 API's to communicate with SQLite3 Database

Here are some of the needed API's that will help Python to communicate with SQLite3.

First step would be to import SQLite3 module in current python module

import sqlite3 as sql

API's and Description 

• connect(database [,timeout ,other optional arguments])

 # program to import SQLite3 database and craete database and database connection

 import sqlite3 as sql

 conn = sql.connect("mydatabase.db")          # creating connection with database(created in current directory)
 print("successfully created connection with mydatabase.db at ", conn)

 conn_1 = sql.connect("../../mydatabase.db")          # creating connection with database(created on specific path)
 print("successfully created connection with mydatabase.db at ", conn_1)

 conn.close()
 conn_1.close()

 #Output

 successfully created connection with mydatabase.db at  <sqlite3.Connection object at 0x000001A4CA6D7730>
 successfully created connection with mydatabase.db at  <sqlite3.Connection object at 0x000001A4CA6D7810>

1. This API opens a connection to the SQLite database file.
2. If database is not present, it will create new database.
3. A specific filename for database with extension *.db or *.sqlite can be passed to create database in current folder.
4. A specific filename for database with the required path as well if you want to create a database anywhere else except in the current directory.
5. If database is opened successfully, it returns a connection object.
6. When a database is accessed by multiple connections, and any one among those processes modifies the database, the SQLite database is locked til that transaction is committed. 
7. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception.
8. The default for the timeout parameter is 5.0 (five seconds).

• cursor([cursorClass])

 # program to import SQLite3 database and craete database and database connection

 import sqlite3 as sql

 conn = sql.connect("mydatabase.db")          # creating connection with database(created in current directory)
 print("successfully created connection with mydatabase.db at ", conn)

 curs = conn.cursor()       # creating cursor to database
 print("successfully created cursor to mydatabase.db at ", curs)

 conn.close()

 #Output

 successfully created connection with mydatabase.db at  <sqlite3.Connection object at 0x00000275352D7730>
 successfully created cursor to mydatabase.db at  <sqlite3.Cursor object at 0x000002753529F6C0>

1. This API helps to create a cursor which will be used throughout of the database programming with Python .
2. This method accepts a single optional parameter cursorClass.
3. If supplied, this must be a custom cursor class that extends sqlite3.Cursor.

• execute(sql [, optional parameters])

 # program

 import sqlite3 as sql

 conn = sql.connect("mydatabase.db")     # creating connection with database(created in current directory)
 curs = conn.cursor()                    # creating cursor to database

 curs.execute("create table Employee_Data(Id number primary key, Name text)")
 print("Table Created Successfully")

 conn.close()

 #Output

 Table Created Successfully

1. This API help to execute an SQL statement.
2. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals).
3. The sqlite3 module supports two kinds of placeholders: question marks and named placeholders.
4. This allows to communicate with database for operations like
1. Create a table in database
2. Write data in table
3. Update data in table
4. Read data from table
5. Delete data in table

Examples >>>>>

• cursor.executemany(sql, seq_of_parameters)

1. This API helps to execute an SQL command against all parameter sequences or mappings found in the sequence sql.

• executescript(sql_script)

 # program to execute many scripts

 import sqlite3 as sql

 conn = sql.connect("mydatabase.db")     # creating connection with database(created in current directory)
 curs = conn.cursor()                    # creating cursor to database

 curs.executescript("create table Employee_Data(Id number primary key, Name text);"
             "create table Employee_Salary(Id number primary key, Salary number)")
 print("Table Created Successfully")

 conn.close()

 #Output

 Tables Created Successfully

1. This API helps to execute multiple SQL statements at once provided in the form of script.
2. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter.
3. Multiple SQL statements should be separated by a semi colon (;).

• total_changes()

 # program to import SQLite3 database and craete database and database connection

 import sqlite3 as sql

 conn = sql.connect("mydatabase.db")     # creating connection with database(created in current directory)
 curs = conn.cursor()                    # creating cursor to database

 curs.executescript("create table Employee_Data(Id number primary key, Name text)")
 curs.executescript("insert into Employee_Data(Id, Name)values(55, 'Techno Xpresss')")
 print("Table Created an data inserted  Successfully")

 print("Total Modifications since database connection is opened; ", conn.total_changes)

 conn.close()

 #Output

 Table Created an data inserted  Successfully
 Total Modifications since database connection is opened;  1

1. This routine returns the total number of database rows that have been modified, inserted, or deleted since the time database connection was opened.

• commit()

 # program 

 import sqlite3 as sql

 conn = sql.connect("mydatabase.db")     # creating connection with database(created in current directory)
 curs = conn.cursor()                    # creating cursor to database

 curs.execute("create table Employee_Data(Id number primary key, Name text)")
 curs.executescript("insert into Employee_Data(Id, Name)values(55, 'Techno Xpresss');"
                   "insert into Employee_Data(Id, Name)values(56, 'Er M S Dandyan')")
 print("Table Created an data inserted  Successfully")

 conn.commit()       # updating the modified changes in database to be visible to other connections
 conn.close()

 #Output

 Table Created an data inserted  Successfully

1. This method commits the current transaction(save the changes in database).
2. If you don't call this method, anything you did since the last call to commit() is not visible from other database connections.

• rollback()

 conn.rollback()

1. This API rolls back any changes to the database since the last call to commit()

• close()

 conn.close()

1. This API helps to close the database connection.
2. Note that close() does not automatically call commit().
3. If try directly to close database connection without calling commit() first, changes from last commit() will be lost!

• fetchone()

 data_in_table = curs.fetchone()

1. This API helps to fetch the next row of a query result set.
2. It returns a single sequence(single row).
3. It returns None when no more data is available

• fetchmany([size = cursor.arraysize])

 data_in_table = curs.fetchmany(5)

1. This API  fetches the  set of rows of a query result.
2. It returns a list of data fetched.
3. An empty list is returned when no more rows are available.
4. The method tries to fetch as many rows as indicated by the size parameter(5 in this example).

• fetchall()

 data_in_table = curs.fetchall()

1. This API helps to fetch all rows of a query result.
2. It will return a list of data.
3. An empty list is returned when no rows are available.