Day 3 Running SQL Queries .ipynb

Kernel: Python 3 (ipykernel)

Python offers several libraries and modules for interacting with SQL databases. Here are some of the most popular ones:

1. SQLite3: This is a built-in library in Python, which provides an interface for SQLite databases

2. SQLAlchemy: A powerful ORM library that supports multiple database backends (SQLite, MySQL, PostgreSQL, and more)

3.pyodbc: A library for accessing databases with ODBC (Open Database Connectivity)

4.MySQL Connector/Python: This is an official Oracle-supported driver to connect to MySQL databases

These Libraries provide various functionalities and are suitable for different types of SQL databases. Depending on your specific needs and the type of database you are working with, you can choose the appropriate library.

Steps:

• Create a conn

• Cursor()

• exceute()

• close connection()

SQLite3

import sqlite3

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

# Create a cursor object to interact with the database
cursor = conn.cursor()

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

# Insert data
cursor.execute('''
INSERT INTO users (name, age) VALUES (?, ?)
''', ('John', 30))

# Commit the transaction
conn.commit()

# Query data
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
conn.close()

(1, 'John', 30)

import sqlite3

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

# Create a cursor object to interact with the database
cursor = conn.cursor()

# Create a table with additional columns
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    email TEXT,
    city TEXT
)
''')

<sqlite3.Cursor at 0x206df9cf420>

import sqlite3

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

# Create a cursor object to interact with the database
cursor = conn.cursor()

# Drop the table if it exists
cursor.execute('DROP TABLE IF EXISTS users')

# Create a table with additional columns
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    email TEXT,
    city TEXT
)
''')

# Insert data
users_data = [
    ('Alice', 30, '[email protected]', 'New York'),
    ('Sam', 29, '[email protected]', 'Los Angeles'),
    ('Tam', 40, '[email protected]', 'Chicago'),
    ('John', 35, '[email protected]', 'Houston'),
    ('Jane', 28, '[email protected]', 'Phoenix'),
    ('Mike', 32, '[email protected]', 'Philadelphia'),
    ('Sue', 31, '[email protected]', 'San Antonio'),
    ('Bob', 27, '[email protected]', 'San Diego'),
]

# Bulk insert
cursor.executemany('''
INSERT INTO users (name, age, email, city) VALUES (?, ?, ?, ?)
''', users_data)

# Commit the transaction
conn.commit()

# Query data to verify insertion
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
conn.close()

(1, 'Alice', 30, '[email protected]', 'New York')
(2, 'Sam', 29, '[email protected]', 'Los Angeles')
(3, 'Tam', 40, '[email protected]', 'Chicago')
(4, 'John', 35, '[email protected]', 'Houston')
(5, 'Jane', 28, '[email protected]', 'Phoenix')
(6, 'Mike', 32, '[email protected]', 'Philadelphia')
(7, 'Sue', 31, '[email protected]', 'San Antonio')
(8, 'Bob', 27, '[email protected]', 'San Diego')

import pandas as pd
conn = sqlite3.connect('Demo.db')
##  **Convert the table into a pandas DataFrame:**

# Read SQL query into a DataFrame
df = pd.read_sql_query('SELECT * FROM users', conn)


# Close the connection
conn.close()
df

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.orm import sessionmaker

# Connect to the database
engine = create_engine('sqlite:///example.db')
metadata = MetaData()

# Define a table
d1= Table('d', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('age', Integer)
)

# Create the table
metadata.create_all(engine)

# Insert data
with engine.connect() as connection:
    connection.execute(d1.insert(), {'name': 'Ashi', 'age': 24})
    connection.execute(d1.insert(), {'name': 'Ram', 'age': 20})
    connection.execute(d1.insert(), {'name': 'Visa', 'age': 21})

# Query data
Session = sessionmaker(bind=engine)
session = Session()
result = session.query(d1).all()

for row in result:
    print(row)

Quick Practice:

1. : Working with SQLite3

Task 1: Setup and Create a Database Import the sqlite3 module. Create a connection to a new SQLite database called lab.db. Create a table called students with columns id (INTEGER, PRIMARY KEY) and name (TEXT).

2. : Exercise 2: Working with SQLAlchemy

Task 1: Setup and Create a Database Install SQLAlchemy if you haven't already: pip install sqlalchemy Import the necessary modules from SQLAlchemy. Create an SQLite database called lab_alchemy.db. Define a Student class with columns id (INTEGER, primary key) and name (String).

##d2=df.to_sql_query