In this part, you will figure out how to utilize SQLite in Python programs.
Installation
SQLite3 can be incorporated with Python utilizing sqlite3 module, which was composed by Gerhard Haring. It furnishes a SQL interface consistent with the DB-API 2.0 determination portrayed by PEP 249. You don't have to introduce this module independently in light of the fact that it is sent as a matter of course alongside Python form 2.5.x onwards.
To utilize sqlite3 module, you should initially make an association object that addresses the information base and afterward alternatively you can make a cursor object, which will help you in executing all the SQL proclamations.
Python sqlite3 module APIs
Following are significant sqlite3 module schedules, which can do the trick your prerequisite to work with SQLite information base from your Python program. In the event that you are searching for a more modern application, at that point you can investigate Python sqlite3 module's true documentation.
Sr.No. | API & Description |
---|---|
1 |
sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file. You can use ":memory:" to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object. When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds). If the given database name does not exist then this call will create the database. You can specify filename with the required path as well if you want to create a database anywhere else except in the current directory. |
2 |
connection.cursor([cursorClass]) This routine creates a cursor which will be used throughout of your database programming with Python. This method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor. |
3 |
cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style). For example − cursor.execute("insert into people values (?, ?)", (who, age)) |
4 |
connection.execute(sql [, optional parameters]) This routine is a shortcut of the above execute method provided by the cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor's execute method with the parameters given. |
5 |
cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. |
6 |
connection.executemany(sql[, parameters]) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor.s executemany method with the parameters given. |
7 |
cursor.executescript(sql_script) This routine executes multiple SQL statements at once provided in the form of script. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. All the SQL statements should be separated by a semi colon (;). |
8 |
connection.executescript(sql_script) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor's executescript method with the parameters given. |
9 |
connection.total_changes() This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. |
10 |
connection.commit() This method commits the current transaction. If you don't call this method, anything you did since the last call to commit() is not visible from other database connections. |
11 |
connection.rollback() This method rolls back any changes to the database since the last call to commit(). |
12 |
connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost! |
13 |
cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. |
14 |
cursor.fetchmany([size = cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter. |
15 |
cursor.fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available. |
Connect To Database
Following Python code tells the best way to associate with a current information base. In the event that the information base doesn't exist, at that point it will be made lastly a data set item will be returned.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
Here, you can likewise supply data set name as the exceptional name :memory: to make a data set in RAM. Presently, we should run the above program to make our data set test.db in the current index. You can change your way according to your necessity. Keep the above code in sqlite.py record and execute it as demonstrated underneath. On the off chance that the information base is effectively made, at that point it will show the accompanying message.
$chmod +x sqlite.py
$./sqlite.py
Open database successfully
Create a Table
Following Python program will be utilized to make a table in the recently made data set.
#!/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()
At the point when the above program is executed, it will make the COMPANY table in your test.db and it will show the accompanying messages −
Opened database successfully
Table created successfully
INSERT Operation
Following Python program tells the best way to make records in the COMPANY table made in the above model.
#!/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()
At the point when the above program is executed, it will make the given records in the COMPANY table and it will show the accompanying two lines −
Opened database successfully
Records created successfully
SELECT Operation
Following Python program tells the best way to get and show records from the COMPANY table made in the above model.
#!/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()
At the point when the above program is executed, it will create the accompanying outcome.
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
Following Python code tells the best way to utilize UPDATE explanation to refresh any record and afterward bring and show the refreshed records 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()
At the point when the above program is executed, it will create the accompanying outcome.
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
Following Python code tells the best way to utilize DELETE proclamation to erase any record and afterward bring and show the excess 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 result.
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