database python connection basic Sqlalchemy & Pandas
connecting a database through Python
Sqlite
Sqlalchemy
Pandas
Create a database engine¶
- SQLite database
- simple and fast
- SQLAlchemy
- Works with many Relational Database Management Systems
- databasetype:///schema_name.extention
- Works with many Relational Database Management Systems
In [3]:
from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.sqlite')
In [5]:
table_names = engine.table_names()
table_names
Out[5]:
example: Chinook Database¶
- http://chinookdatabase.codeplex.com/
- the Chinook database contains information about a semi-fictional digital media store in which media data is real and customer, employee and sales data has been manually created.
In [8]:
# 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)
The Hello World of SQL Queries!¶
- SELECT, in order to retrieve all columns of the table Album in the Chinook database. Recall that the query SELECT * selects all columns.
In [12]:
# 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]
Out[12]:
In [13]:
# Save results of the query to DataFrame
df=pd.DataFrame(ll)
df.head(3)
Out[13]:
Customizing the Hello World of SQL Queries¶
- Select specified columns from a table;
- Select a specified number of rows;
- Import column names from the database table.
In [28]:
# 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
Out[28]:
Filtering your database records using SQL's WHERE¶
In [34]:
# 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)
Out[34]:
Ordering your SQL records with ORDER BY¶
In [37]:
# 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)
Out[37]:
Querying relational databases directly with pandas¶
- df = pd.read_sql_query("queries", engine)
In [40]:
# 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
- compare
In [41]:
# 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))
Pandas for more complex querying¶
- You'll build a DataFrame that contains the rows of the Employee table for which the EmployeeId is greater than or equal to 6 and you'll order these entries by BirthDate.
In [44]:
# 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
Out[44]:
INNER JOIN¶
In [47]:
# 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)
Out[47]:
pandas filter¶
In [50]:
# 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)
Out[50]:
In [ ]:
