Querying relational databases in Python
# Import necessary module from sqlalchemy import create_engine # Create engine: engine engine = create_engine('sqlite:///Chinook.sqlite') # Save the table names to a list: table_names table_names = engine.table_names() # Print the table names to the shell print(table_names)[‘Album’, ‘Artist’, ‘Customer’, ‘Employee’, ‘Genre’, ‘Invoice’, ‘InvoiceLine’, ‘MediaType’, ‘Playlist’, ‘PlaylistTrack’, ‘Track’]
# Import packages from sqlalchemy import create_engine import pandas as pd # Create engine: engine engine = create_engine('sqlite:///Chinook.sqlite') # Open engine connection con = engine.connect() # Perform query: rs rs = con.execute("SELECT * FROM Album") # Save results of the query to DataFrame: df df = pd.DataFrame(rs.fetchall()) # Close connection con.close() # Print head of DataFrame df print(df.head())0 1 2 0 1 For Those About To Rock We Salute You 1 1 2 Balls to the Wall 2 2 3 Restless and Wild 2 3 4 Let There Be Rock 1 4 5 Big Ones 3
# Open engine in context manager # Perform query and save results to DataFrame: df with engine.connect() as con: rs = con.execute("SELECT LastName, Title FROM Employee") # Apply the method fetchmany() to rs in order to retrieve 3 of the records. Store them in the DataFrame df. df = pd.DataFrame(rs.fetchmany(size = 3)) df.columns = rs.keys() # Print the length of the DataFrame df print(len(df)) # Print the head of the DataFrame df print(df.head()) # Create engine: engine engine = create_engine('sqlite:///Chinook.sqlite') # Open engine in context manager # Perform query and save results to DataFrame: df with engine.connect() as con: rs = con.execute('SELECT * FROM Employee WHERE EmployeeId >= 6') df = pd.DataFrame(rs.fetchall()) df.columns = rs.keys() # Print the head of the DataFrame df print(df.head()) # Create engine: engine engine = create_engine('sqlite:///Chinook.sqlite') # Open engine in context manager with engine.connect() as con: rs = con.execute('SELECT * FROM Employee ORDER BY BirthDate') df = pd.DataFrame(rs.fetchall()) df.columns = rs.keys() # Set the DataFrame's column names # Print head of DataFrame print(df.head())Querying relational databases directly with pandas
# Import packages from sqlalchemy import create_engine import pandas as pd # Create engine: engine engine = create_engine('sqlite:///Chinook.sqlite') # Execute query and store records in DataFrame: df df = pd.read_sql_query('SELECT * FROM Album', engine) # Print head of DataFrame print(df.head()) # Open engine in context manager and store query result in df1 with engine.connect() as con: rs = con.execute("SELECT * FROM Album") df1 = pd.DataFrame(rs.fetchall()) df1.columns = rs.keys() # Confirm that both methods yield the same result print(df.equals(df1)) # Import packages from sqlalchemy import create_engine import pandas as pd # Create engine: engine engine = create_engine('sqlite:///Chinook.sqlite') # Execute query and store records in DataFrame: df df = pd.read_sql_query('SELECT * FROM Employee WHERE EmployeeId >= 6 ORDER BY BirthDate', engine) # Print head of DataFrame print(df.head()) import pandas as pd from sqlalchemy import create_engine engine = create_engine('sqlite:///Chinook.sqlite') # Open engine in context manager # Perform query and save results to DataFrame: df with engine.connect() as con: rs = con.execute("SELECT Title, Name FROM Album INNER JOIN Artist on Album.ArtistID = Artist.ArtistID") df = pd.DataFrame(rs.fetchall()) df.columns = rs.keys() # Print head of DataFrame df print(df.head()) import pandas as pd from sqlalchemy import create_engine engine = create_engine('sqlite:///Chinook.sqlite') # Execute query and store records in DataFrame: df df = pd.read_sql_query('SELECT * FROM PlaylistTrack INNER JOIN Track on PlaylistTrack.TrackId = Track.TrackId WHERE Milliseconds < 250000', engine) # Print head of DataFrame print(df.head())