4. Relational database in Python

import pandas as pd

from sqlalchemy import create_engine # Import packages

engine = create_engine ('sqlite:///Northwind.sqlite') # create engine

con = engine.connect() # open engine connection

rs = con.execute(“SELECT * FROM Orders”) # query the database

df = pd.DataFrame(rs.fetchall()) # turn the result into dataframe

df.columns = rs.keys() # set dataframe column names

con.close() # close the connection

print(df.head()) # print the head of the dataframe

import pandas as pd

from sqlalchemy import create_engine # Import packages

engine = create_engine ('sqlite:///Northwind.sqlite') # create engine

with engine.connect() as con: # open engine connection

rs = con.execute(“SELECT OrderID, OrderDate, ShipName FROM Orders WHERE OrderId >= 6”) # query the database

df = pd.DataFrame(rs.fetchmany(size=5)) # turn the result into dataframe

df.columns = rs.keys() # set dataframe column names

print(len(df)) # Print the length of the DataFrame

print(df.head()) # print the head of the dataframe

import pandas as pd

from sqlalchemy import create_engine # Import packages

engine = create_engine('sqlite:///Chinook.sqlite') # Create engine

df = pd.read_sql_query("SELECT * FROM Employee WHERE EmployeeID >= 6 ORDER BY BirthDate", engine) # Execute query and store records in DataFrame: df

print(df.head()) # Print head of DataFrame

import pandas as pd

from sqlalchemy import create_engine # Import packages

engine = create_engine('sqlite:///Chinook.sqlite')

df = pd.read_sql_query("SELECT * FROM PlaylistTrack INNER JOIN Track on PlaylistTrack.TrackId = Track.TrackId WHERE Milliseconds < 250000", engine) # Execute query and store records in DataFrame

print(df.head()) # Print head of DataFrame