Importing Relational Databases

Question 1

module for SQL engines

Answer 1

from sqlalchemy import create_engine

Question 2

creating an engine

Answer 2

engine = create_engine(‘sqlite(dbtype):///filename’)

Question 3

get table names from database

Answer 3

engine.table_names()

Question 4

opening a connection

Answer 4

con = engine.connect()

Question 5

perform an SQL query

Answer 5

rs = con.execute(‘Query’)

Question 6

save results of query to dataframe

Answer 6

pd.DataFrame(rs.fetchall())

Question 7

SQL to DataFrame step by step

Answer 7

1. import create_engine from sqlalchemy
2. create an engine
3. open connection
4. perform SQL query
5. save results to DataFrame

Question 8

Query form

Answer 8

‘SELECT ColumnA, Column B (or *) FROM Table WHERE…(optional) ORDER BY…’

Question 9

querying SQL with pandas

Answer 9

pd.read_sql_query(‘query’, engine)

Question 10

inner join

Answer 10

exploit table relations: SELECT * FROM X INNER JOIN Y on *.X = *.Y