SQLite - Python

installation

SQLite3 sqlite3 module can be used with Python integration. sqlite3 module written by Gerhard Haring. It provides a DB-API 2.0 specification described by PEP 249 is compatible with the SQL interface. You do not need to install the module separately, because Python 2.5.x or later default comes with the module.

In order to use sqlite3 module, you must first create a database connection object representation, and then you can choose to create a cursor object, which will help you perform all of the SQL statements.

Python sqlite3 module API

The following are important sqlite3 module program to meet your needs using SQLite database in Python programs. If you need more details, see the official documentation Python sqlite3 module.

Connect to the database

The following Python code shows how to connect to an existing database. If the database does not exist, it is created, and finally returns a database object.

#! / Usr / bin / python

import sqlite3

conn = sqlite3.connect ( 'test.db')

print "Opened database successfully";

Here, you can also copy the name of the database for a specificname: memory:, This will create a database in RAM.Now, let's run the above program, create our databasetest.db in the current directory.You can change the path as needed. Save the above code to sqlite.py file and press shown is performed as follows. If the database is successfully created, it will display the message shown below:

$ Chmod + x sqlite.py
$. / Sqlite.py
Open database successfully

Create a table

The following Python code will be used to create a table in the database previously created:

#! / Usr / bin / python

import sqlite3

conn = sqlite3.connect ( 'test.db')
print "Opened database successfully";

conn.execute ( '' 'CREATE TABLE COMPANY
       (ID INT PRIMARY KEY NOT NULL,
       NAME TEXT NOT NULL,
       AGE INT NOT NULL,
       ADDRESS CHAR (50),
       SALARY REAL); '' ')
print "Table created successfully";

conn.close ()

When the above procedure, it creates COMPANY table intest.db and displays the message shown below:

Opened database successfully
Table created successfully

INSERT operation

The following Python program shows how to create records in the COMPANY table created above:

#! / Usr / bin / python

import sqlite3

conn = sqlite3.connect ( 'test.db')
print "Opened database successfully";

conn.execute ( "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00) ");

conn.execute ( "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00) ");

conn.execute ( "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00) ");

conn.execute ( "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00) ");

conn.commit ()
print "Records created successfully";
conn.close ()

The above program is executed, it will be created in the COMPANY table for a given record, and displays the following two lines:

Opened database successfully
Records created successfully

SELECT operations

The following Python program shows how to get from the COMPANY table created earlier and displays the record:

#! / Usr / bin / python

import sqlite3

conn = sqlite3.connect ( 'test.db')
print "Opened database successfully";

cursor = conn.execute ( "SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID =", row [0]
   print "NAME =", row [1]
   print "ADDRESS =", row [2]
   print "SALARY =", row [3], "\ n"

print "Operation done successfully";
conn.close ()

When the above program is executed, it will produce the following results:

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

UPDATE operation

The following Python code shows how to use the UPDATE statement to update any records, then get from COMPANY table and display the updated record:

#! / Usr / bin / python

import sqlite3

conn = sqlite3.connect ( 'test.db')
print "Opened database successfully";

conn.execute ( "UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit
print "Total number of rows updated:", conn.total_changes

cursor = conn.execute ( "SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID =", row [0]
   print "NAME =", row [1]
   print "ADDRESS =", row [2]
   print "SALARY =", row [3], "\ n"

print "Operation done successfully";
conn.close ()

When the above program is executed, it will produce the following results:

Opened database successfully
Total number of rows updated: 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

DELETE operation

The following Python code shows how to use the DELETE statement deletes any records, and then taken from the COMPANY table and displays the remaining recording:

#! / Usr / bin / python

import sqlite3

conn = sqlite3.connect ( 'test.db')
print "Opened database successfully";

conn.execute ( "DELETE from COMPANY where ID = 2;")
conn.commit
print "Total number of rows deleted:", conn.total_changes

cursor = conn.execute ( "SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID =", row [0]
   print "NAME =", row [1]
   print "ADDRESS =", row [2]
   print "SALARY =", row [3], "\ n"

print "Operation done successfully";
conn.close ()

When the above program is executed, it will produce the following results:

Opened database successfully
Total number of rows deleted: 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully