from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.sqlite')
table_names = engine.table_names()
table_names
# Import necessary module
from sqlalchemy import create_engine
# Create engine: engine
engine = create_engine('sqlite:///Chinook_Sqlite.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)
# Import packages
from sqlalchemy import create_engine
import pandas as pd
# Create engine: engine
engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')
# Open engine connection
con = engine.connect()
# Perform query: rs
rs = con.execute('select * from Album')
# Save results of the query to list: ll
ll = rs.fetchall()
# Close connection
con.close()
# Print head of list
print len(ll),type(ll)
ll[:3]
# Save results of the query to DataFrame
df=pd.DataFrame(ll)
df.head(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")
df = pd.DataFrame(rs.fetchmany(size=3))
# Using the rs object, set the DataFrame's column names to the corresponding names of the table columns.
df.columns = rs.keys()
# Print the length of the DataFrame df
print(len(df))
# Print the head of the DataFrame df
df
# Create engine: engine
engine = create_engine('sqlite:///Chinook_Sqlite.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.shape
df.head(1)
# Create engine: engine
engine = create_engine('sqlite:///Chinook_Sqlite.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())
# Set the DataFrame's column names
df.columns = rs.keys()
# Print head of DataFrame
print df.shape
df.head(3)
# Import packages
from sqlalchemy import create_engine
import pandas as pd
# Create engine: engine
engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')
# Execute query and store records in DataFrame: df
df = pd.read_sql_query("SELECT * FROM Album", engine)
print df.shape
# Open engine in context manager
# Perform query and save results to DataFrame: 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: does df = df1 ?
print(df.equals(df1))
# Import packages
from sqlalchemy import create_engine
import pandas as pd
# Create engine: engine
engine = create_engine('sqlite:///Chinook_Sqlite.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.shape
df
# 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.shape
df.head(3)
# 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.shape
df.head(3)