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)
Be First to Comment