SQLite - Python

This API opens a link to the SQLite database file database. You can use ":memory:" to open a database connection to the database in RAM instead of on disk. If the database is opened successfully, a connection object is returned.

When a database is accessed by multiple connections and one of them modifies the database, the SQLite database is locked until the transaction is committed. The timeout parameter indicates the duration for which the connection waits for locking until an exception occurs and the connection is disconnected. The timeout parameter defaults to 5.0 (5 seconds).

If the given database name filename does not exist, this call will create a database. If you do not want to create the database in the current directory, you can specify the file name with a path, which will allow you to create the database anywhere.

cursor, will be used in Python database programming. This method accepts a single optional parameter cursorClass. If this parameter is provided, it must be a custom cursor class that extends sqlite3.Cursor.

For example: cursor.execute("insert into people values ​​(?, ?)", (who, age))

Connecting to Database

The following Python code shows how to connect to an existing database. If the database does not exist, it will be created and a database object will be returned.

#!/usr/bin/python

import sqlite3

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

print "Opened database successfully";

Here you can also copy the database name to a specific name :memory:, which will create a database in RAM. Now, let's run the above program to create our database test.db in the current directory. You can change the path as needed. Save the above code into the sqlite.py file and execute it as shown below. If the database is created successfully, a message like the one shown below will be displayed:

$chmod +x sqlite.py
$./sqlite.py
Open database successfully

Create table

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

#!/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 program is executed, it creates the COMPANY table in test.db and displays the message shown below:

Opened database successfully
Table created successfully

INSERT operation

below The Python program shows how to create a record 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()

When the above program is executed, it creates the given record in the COMPANY table and displays the following two lines:

Opened database successfully
Records created successfully

SELECT Operation

The following Python program shows how to obtain and display records from the COMPANY table created earlier:

#!/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 produces 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 record, and then get and display the updated record from the COMPANY table:

#!/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()

The above program execution , it produces the following result:

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 to delete any record and then get and display the remaining records from the COMPANY table:

#!/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

php.cn

HomeCourseArticleQ&ADictionaryManualDownloadsearch APPDownload