Python SQLite- Connect, Cursor and Execute

Web applications or desktop applications need database to store the data. SQLite is a lightweight and efficient Relational Database Management System (RDBMS). It integrates easily if you use it as a backend of your Python application. SQLite is a C library and that makes it a highly dynamic way to run SQL commands in the python environment. If you know the basic concepts of SQL then SQLite your learning curve will be quite low. Otherwise also SQLite is easy to use. Integrating Python-SQLite, the SQL commands can be run very easily within the Python codes.

Python SQLite- Where to Start?

To use Python SQLite together you need to import SQLite library in the python programs. It is done with by writing following command at the beginning of the program or on Python command prompt

 >> import sqlite3

Note : You don’t need to install sqlite3 module. It is included in the standard library (since Python 2.5).

Making Database Connection

Before you submit any SQL commands on your SQLite database in a Python program you have to establish the database connection. SQLite provides connect method for this purpose.  The connect method returns a connect handle that represents the connection to the database. All future database activities must be done through this handle.

Syntax

Connection-name=sqlite3.connect(‘database-name.db’)

Connect-name is the name of the handle returned by connect method. Since connect method is associated with SQLlite, it must always be called with the name of the class sqlite3 (pre-installed in your Python2.5 onwards installation).

Creating a Cursor

After creating a connection, to execute SQL statements on SQLite Database, you need a cursor. Its like a temporary location that stores results of the queries and are updated in DB only when you commit them with commit method on connection object.

Syntax

cursor-name=connect_object.cursor()

cursor -name is the name of the cursor to be given by you.  connect_object  is the name of the connect handle already declared with connect method. Cursor is the method to create a cursor

Executing SQLite Queries

To run SQL commands you will use the execute method. It can be called with a cursor object and you get back the set of rows affected by the SQL command. All the SQL queries can be run inside the execute command enclosing it in single quotes or triple single quotes directives.

Note : it is better to use ‘’’’ ’’’ directive(triple single quotes) to avoid the problems imposed by SQL injections.

Syntax

Cursor_name.execute(query as a string):

commit() is used to save the changes made in the database. It is always used with connection object

Syntax

connect_object.commit():

Example of some basic commands used in Python SQLite

airplanes=sqlite3.connect('data.db')

In the above statement a connection is established with the data.db database using the ‘airplanes’ variable name. This variable can be used further to create and alter tables inside that database.

c = airplanes.cursor()

In the above statement a cursor is created with the airplanes connection. This variable can be used to execute the SQL DML statements on that database.

airplanes.execute('''create table airline_data (iatacode text, AirlineName text, value real, fleetsize integer)’’’)
c.execute('''INSERT INTO airline_data VALUES ('AI','Air India','83776266','114')''')
c.execute('''INSERT INTO airline_data VALUES ('BA','British Airways','283776266','205')''')
c.execute('''INSERT INTO airline_data VALUES ('EI','Europe International','3333','300')''')

Displaying the full table in the python console:

for row in c.execute('SELECT * FROM airline_data'):
        print(row)
python SQLite output

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *