
In [24]:
import sqlalchemy as db
import pymysql
import pandas as pd
engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila?host=localhost?port=3306") # connect to server
# PostgreSQL
#engine = create_engine('postgresql://usr:pass@localhost:5432/sqlalchemy')
In [25]:
sql_query = """
select * from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[25]:
In [26]:
sql_query = """
select concat(first_name, " ", last_name) as Actor_Name
from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[26]:
In [27]:
sql_query = """
select actor_id, first_name, last_name from actor
where first_name = "Joe"
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[27]:
In [28]:
sql_query = """
select * from actor
where last_name like "%%GEN%%"
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[28]:
In [29]:
sql_query = """
select last_name, first_name from actor
where last_name like "%%LI%%"
order by last_name, first_name
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return
Out[29]:
In [30]:
sql_query = """
select country_id, country from country
where country IN ("China", "Afghanistan", "Bangladesh");
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return
Out[30]:
In [31]:
sql_query = """
select *
from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[31]:
In [32]:
sql_query = """
select *
from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[32]:
In [33]:
sql_query = """
select last_name,
count(last_name) as Name_Count
from actor
group by last_name
order by last_name asc
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[33]:
In [34]:
sql_query = """
select last_name,
count(last_name) as Name_Count
from actor
group by last_name
Having Name_Count >= 2
order by last_name asc
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[34]:
In [36]:
sql_query = """
select * from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[36]:
In [37]:
sql_query = """
select * from address
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[37]: