How to use databases and SQLite in Python?
This tutorial discusses how to use databases and SQLite in Python with a simple snippet (fragment) of code.
Video tutorial
The objective of this Tutorial is to Understand:
What is a database?
How to create a database table using SQLite database?
How to insert data into a database table using SQLite database?
What are the methods to retrieve data from the database table using the SQLite database?
How to update data or delete a row in the database table using SQLite database?
How to drop a table from the database using SQLite database?
What is a database?
A database is a file that is organized for storing data. Data in the database is arranged in the form of tables, rows, and columns.
There are many different database systems available such as Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and SQLite. Each of these databases is used for a variety of purposes. This tutorial discusses how to use SQLite in Python, as it is inbuilt in Python and easy to use.
What are the Functions available in SQLite?
The following diagram shows the functions available in the SQLite database.
First, we need to connect to the database using connect() function. connect() function accepts one parameter that is the name of the database. If the database is already present in the system then connect() function opens the database. Otherwise, the connect() function creates a new database.
Once the database is created using connect() function. The database looks like a file to our program. We open the normal file using an open() function and then perform read and write operations. Similarly, the cursor() function is used to open the database.
After the database is opened using the cursor() function, we can perform different operations on the database. Like, we can create a new table, add new data into the table. We can update the data in the table and delete the rows of the table. Also, we can drop the entire table. Finally, we can fetch the content of the table using fetchone() or fetchall() function and display it to the user.
How to create a database table using an SQLite database?
In the following fragment of Python code, we have established the connection to the database using connect() function. Then the database was opened using the cursor() function. Later, we have created a table by executing a CRETE TABLE query using the execute() function. Finally, we have closed the connection using the close() function.
import sqlite3
conn = sqlite3.connect('StudentInfo')
cur = conn.cursor()
cur.execute('CREATE TABLE student (name TEXT, rno INTEGER)')
conn.close()How to insert data into database table?
The next Fragment of Python code shows how to insert data into the database.
import sqlite3
conn = sqlite3.connect('StudentInfo')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS student')
cur.execute('CREATE TABLE student (name TEXT, rno INTEGER)')
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Mahesh', 45))
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Rahul', 30))
conn.close()How to retrieve data from the database table using SQLite?
The data from the database is extracted using a SELECT query. Then three methods are used to demonstrate how to display the results to the user. First, The return value of the SELECT query was stored into a variable, data in this example. Then the one row from the data is extracted using the for loop and displayed to the user. Second, the fetchone() function is used to extract the first rows from the cursor and displayed them. Third, fetchall() function is used to extract all the rows from the cursor and displayed them.
import sqlite3
conn = sqlite3.connect('StudentInfo')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS student')
cur.execute('CREATE TABLE student (name TEXT, rno INTEGER)')
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Mahesh', 45))
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Rahul', 30))
data = cur.execute('SELECT * FROM student')
print ("Data in the table using for loop:")
for row in data:
print (row)
cur.execute('SELECT * FROM student')
print ("Data in the table using fetchone() function:")
print (cur.fetchone())
cur.execute('SELECT * FROM student')
print ("Data in the table using fetchall() function:")
print (cur.fetchall())
conn.close()Output:
Data in the table using for loop: (‘Mahesh’, 45) (‘Rahul’, 30)
Data in the table using fetchone() function: (‘Mahesh’, 45)
Data in the table using fetchall() function: [(‘Mahesh’, 45), (‘Rahul’, 30)]
How to update data in the database table using the SQLite?
The data in the database is updated using the SET clause and the result is displayed to the user. ORDER BY clause is used to display the data based on a particular attribute. In this example ORDER BY rno is used hence the result will be sorted based on rno attribute.
import sqlite3
conn = sqlite3.connect('StudentInfo')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS student')
cur.execute('CREATE TABLE student (name TEXT, rno INTEGER)')
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Mahesh', 45))
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Rahul', 30))
cur.execute('SELECT * FROM student')
print ("Data in the table before Update:")
print (cur.fetchall())
cur.execute('UPDATE student SET rno = 40 WHERE rno = 45')
cur.execute('UPDATE student SET rno = 10 WHERE name =\'Rahul\'')
cur.execute('SELECT * FROM student ORDER BY rno')
print ("Data in the table after Update:")
print (cur.fetchall())
conn.close()Output
Data in the table before Update: [(‘Mahesh’, 45), (‘Rahul’, 30)]
Data in the table after Update: [(‘Rahul’, 10), (‘Mahesh’, 40)]
How to drop rows from a table or table from the database using the SQLite?
import sqlite3
conn = sqlite3.connect('StudentInfo')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS student')
cur.execute('CREATE TABLE student (name TEXT, rno INTEGER)')
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Mahesh', 45))
cur.execute('INSERT INTO student (name, rno) VALUES (?, ?)',('Rahul', 30))
cur.execute('SELECT * FROM student')
print ("Data in the table before Delete:")
print (cur.fetchall())
cur.execute('DELETE FROM student WHERE rno = 45')
cur.execute('SELECT * FROM student')
print ("Data in the table After Delete:")
print (cur.fetchall())
conn.close()Output
Data in the table before Delete: [(‘Mahesh’, 45), (‘Rahul’, 30)]
Data in the table After Delete: [(‘Rahul’, 30)]
Summary:
This tutorial discusses how to use databases and SQLite in Python with a simple snippet (fragment) of code. Do like and share with your friends and subscribe to the Youtube channel for more videos.