SQLite is a self-contained, serverless, and zero-configuration SQL database engine that is ideal for small to medium-sized applications.
👋 Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
To use SQLite in Python, you will first need to install the sqlite3 module. You can do this using the pip package manager by running the following command:
pip install sqlite3
Once the sqlite3 module is installed, you can use it to connect to an existing SQLite database or create a new one. To create a new database, you can use the sqlite3.connect()
function, which takes the name of the database file as an argument. If the database file does not exist, it will be created for you.
import sqlite3
# Connect to a new database or create one if it doesn't exist
conn = sqlite3.connect('mydatabase.db')
To create a new table in the database, you can use the CREATE TABLE
SQL command. For example, to create a table called employees
with columns for the employee’s ID, name, and salary, you could use the following code:
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
salary REAL
)
''')
To insert data into the table, you can use the INSERT INTO
SQL command. For example, to insert a new employee into the employees
table, you could use the following code:
cursor = conn.cursor()
cursor.execute('''
INSERT INTO employees (id, name, salary)
VALUES (?, ?, ?)
''', (1, 'John Smith', 75000))
To retrieve data from the table, you can use the SELECT
SQL command. For example, to retrieve all employees from the employees
table, you could use the following code:
cursor = conn.cursor()
cursor.execute('''
SELECT * FROM employees
''')
employees = cursor.fetchall()
for employee in employees:
print(employee)
You can also use the WHERE
clause to filter the results of a SELECT
query. For example, to retrieve only employees with a salary greater than $50,000, you could use the following code:
cursor = conn.cursor()
cursor.execute('''
SELECT * FROM employees
WHERE salary > 50000
''')
employees = cursor.fetchall()
for employee in employees:
print(employee)
To update data in the table, you can use the UPDATE
SQL command. For example, to give all employees a 10% raise, you could use the following code:
cursor = conn.cursor()
cursor.execute('''
UPDATE employees
SET salary = salary * 1.1
''')
When you’re finished with the connection you can close it like so:
conn.close()