In this Python Database Tutorial we want to learn about SQLite, so as you know that Python is a powerful programming language and it is commonly used for different applications, including web development, data analysis and scientific computing. in this SQLite Tutorial we want to talk about Python Database programming with SQLite. but first of all let’s talk about SQLite.
What is SQLite ?
SQLite is popular database management system and it is used by Python developers for different tasks. SQLite is serverless, self contained, zero configuration and transactional database engine, SQLite can be embedded in most modern operating systems. this means that it is lightweight and easy to use, and it is one of the best choice for small to medium sized applications.
In this Python SQLite Tutorial, we want to cover the basics of working with SQLite databases in Python. we will go through the steps of creating and connecting to a database, inserting and retrieving data and updating and deleting data.
For working with SQLite database in Python, we need to first create a database and connect to it. for creating a new database, we simply need to import the SQLite3 module and execute this code.
|
1 2 |
import sqlite3 conn = sqlite3.connect('codeloop.db') |
In the above code, we are going to create a new database at name of codeloop.db and after that we use connect() method of the sqlite3 module. if the database already exists, connect() method will connect to it. if it does not exist, it will create a new one.
We have our database, now we need to create our table in the database, we can use this code, this code will create employees table in our database, but right now we don’t have any data in our database.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('codeloop.db') # create a table conn.execute('''CREATE TABLE employees (name TEXT, age INT, salary REAL)''') |
You can see that we have our SQLite table.
After that we have created and connected to a database, we can start inserting data into it. for inserting data into SQLite database, we use execute() method of the database connection object, we want to add two data in the table.
|
1 2 3 4 5 6 7 8 |
import sqlite3 conn = sqlite3.connect('codeloop.db') #Inserting data to the table conn.execute("INSERT INTO employees (name, age, salary) VALUES ('Codeloop', 10, 30000)") # commit changes to the database conn.commit() |
This will be the result
For retrieving data from SQLite database, we can use execute() method of the database connection object with SELECT statement, in this code we are selecting all records from a table called employees and print each row to the console. execute() method returns a cursor object that we can iterate over to retrieve the rows.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('codeloop.db') cursor = conn.execute("SELECT * FROM employees") for row in cursor: print(row) |
This will be the result
For updating data in SQLite database, we use execute() method of the database connection object with an UPDATE statement, this code will update the name.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('codeloop.db') conn.execute("UPDATE employees SET name = 'updated' WHERE name = 'Codeloop'") conn.commit() |
For deleting data from SQLite database, we use execute() method of the database connection object with DELETE statement.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('geekscoders.db') conn.execute("DELETE FROM employees WHERE name = 'updated'") conn.commit() |
Subscribe and Get Free Video Courses & Articles in your Email