Initially, it is essential to install both Python and SQLite on your system.
Install Python
Use the following code:
sudo apt-get update
sudo apt-get upgrade python
By pressing the 'y' key, the installation process will conclude in a matter of seconds.
Install SQLite
Installation steps
type in the following command:
sudo apt-get install sqlite3 libsqlite3-dev
Following the installation process, verify the installation by accessing the SQLite terminal, which will display a prompt along with version information.
sqlite3
Go to desired folder and create database:
sqlite3 database.db
It will generate a file named database.db in the directory specified when you issued the command.
To verify the creation of your database, execute the subsequent command within the sqlite3 terminal:
.databases
Note: To connect SQLite with Python, you do not need to install the connection module separately because its being shipped by default along with Python version 2.5.x onwards.
SQLite with Python
Establish a Python file named "connect.py" that contains the code provided below:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('logicpractice.db')
print "Opened database successfully";
Run the subsequent command in the command prompt:
python connect.py
At this point, a connection has been established with the logicpractice database. You are now ready to proceed with the creation of a table.
Create a table
Establish a table named "Employees" in the database known as "logicpractice".
Generate a Python file named "createtable.py" that includes the subsequent code:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('logicpractice.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE Employees
(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()
Run the subsequent command in the command prompt:
python createtable.py
In the "logicpractice" database, a table named "Employees" has been established.
Insert Records
Insert some records in "Employees" table.
Generate a Python file named "connection.py" that includes the subsequent code:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('logicpractice.db')
print "Opened database successfully";
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Ajeet', 27, 'Delhi', 20000.00 )");
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 22, 'London', 25000.00 )");
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Mark', 29, 'CA', 200000.00 )");
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Kanchan', 22, 'Ghaziabad ', 65000.00 )");
conn.commit()
print "Records inserted successfully";
conn.close()
Run the subsequent command in the command prompt:
python connection.py
Records are inserted successfully.
Select Records
You can now retrieve and present your data from the "Employees" table utilizing the SELECT statement.
Establish a Python file named "select.py" containing the subsequent code:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('logicpractice.db')
data = conn.execute("select * from Employees");
for row in data:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
conn.close();
Run the subsequent command in the command prompt:
python select.py
See all the records you have inserted before.
Using the same methods, you can modify and remove entries from a table in an SQLite database utilizing Python.
