SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL using Python and SQLite

SQLite is a C library that provides a lightweight disk-based database. It doesn't require a separate server process, which makes it a popular choice for embedded database systems. Python's standard library includes a module called sqlite3 intended for working with SQLite.

Below are steps and examples on how to use SQLite with Python:

1. Creating a Connection:

Before you can use an SQLite database, you need to connect to it. This can be done using the connect() function from the sqlite3 module.

import sqlite3

# Connect to a database (or create one if it doesn't exist)
conn = sqlite3.connect('example.db')

2. Creating a Table:

To create a table, you can define your SQL statement and then use the execute() method.

cursor = conn.cursor()

# Create a table
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY, 
    name TEXT, 
    age INTEGER
)
''')
conn.commit()

3. Inserting Data:

You can insert data using the INSERT INTO SQL statement.

# Insert a record
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
conn.commit()

4. Querying Data:

# Select records
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
rows = cursor.fetchall()

for row in rows:
    print(row)

5. Updating Data:

# Update record
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))
conn.commit()

6. Deleting Data:

# Delete record
cursor.execute("DELETE FROM users WHERE name = ?", ("Alice",))
conn.commit()

7. Using with Pandas:

The pandas library has built-in support for SQLite. This is especially useful when dealing with large data or when you want to perform operations on dataframes.

import pandas as pd

# Reading data directly into a DataFrame
df = pd.read_sql_query("SELECT * FROM users", conn)

# Writing data from a DataFrame into the database
df.to_sql('users', conn, if_exists='replace', index=False)

8. Closing the Connection:

Once you're done with your operations, remember to close the connection to the database.

conn.close()

Additional Tips:

Transactions: SQLite supports transactions. You can start a transaction with BEGIN TRANSACTION and complete it with COMMIT. If you want to rollback, you can use ROLLBACK.
Parameterized Queries: As seen in the examples above, always use parameterized queries (?) when inserting or updating data. This prevents SQL injection attacks.
Database in Memory: If you want a temporary database for processing that doesn't write to the disk, you can connect to the special name :memory:.
```
conn = sqlite3.connect(':memory:')
```

SQLite is a great choice for lightweight applications, rapid prototyping, or as an embedded database solution. When combined with Python, it provides a powerful toolset for data manipulation and storage.

SQLite Python Create Table Example:

Description: Creating a table in SQLite using Python involves defining a SQL CREATE TABLE statement.

Code:

import sqlite3

# Connect to SQLite database (creates if not exists)
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL
    )
''')

# Commit changes and close connection
conn.commit()
conn.close()

Executing SQL Queries in Python with SQLite:

Description: Executing SQL queries in Python involves using the execute method of the cursor object.

Code:

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Execute a simple query
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

# Print the results
for row in rows:
    print(row)

# Close connection
conn.close()

Connecting Python to SQLite Database:
- Description: Establishing a connection to an SQLite database using the sqlite3 module in Python.
- Code:
```
import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
```

Python SQLite SELECT Statement Example:

Description: Performing a SELECT query in SQLite to retrieve data from a table.

Code:

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Execute SELECT query
cursor.execute('SELECT * FROM users WHERE id = ?', (1,))
row = cursor.fetchone()

# Print the result
print(row)

# Close connection
conn.close()

Python SQLite INSERT INTO Example:

Description: Inserting data into an SQLite table using the INSERT INTO statement.

Code:

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert data into the table
cursor.execute('INSERT INTO users (username, email) VALUES (?, ?)', ('john_doe', 'john@example.com'))

# Commit changes and close connection
conn.commit()
conn.close()

Python SQLite Update and Delete Queries:

Description: Updating and deleting data in an SQLite table using UPDATE and DELETE statements.

Code:

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Update data in the table
cursor.execute('UPDATE users SET email = ? WHERE id = ?', ('new_email@example.com', 1))

# Delete data from the table
cursor.execute('DELETE FROM users WHERE id = ?', (2,))

# Commit changes and close connection
conn.commit()
conn.close()

Python SQLite and Parameterized Queries:

Description: Using parameterized queries to prevent SQL injection attacks and improve code readability.

Code:

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Parameterized query
user_id = 1
cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
row = cursor.fetchone()

# Print the result
print(row)

# Close connection
conn.close()