import sqlite3
conn = sqlite3.connect('mtcars.sqlite')
c = conn.cursor()

"""
Explanation: Introduction to Databases and SQL

Authors: Ian Dennis Miller
Research field: Social Psychology - Social Complexity
Lesson topic: Databases and SQL
Lesson content URL: https://github.com/UofTCoders/studyGroup/tree/gh-pages/lessons/sql

Plan

open sqlite3 database
send a query
parse and print the result
import from sqlite directly into pandas
End of explanation
"""


# create and execute our query
cylinders = 4
cursor = c.execute("SELECT * FROM results WHERE cyl = {0}".format(cylinders))

"""
Explanation: select cars with 4 cylinders
End of explanation
"""


# fetch a list of the column names
column_names = list(map(lambda x: x[0], cursor.description))

# fetch all rows and map column names onto them
rows = map(lambda x: dict(zip(column_names, x)), cursor.fetchall())

# print the name and MPG for each result
for row in rows:
    print("{name}:\t{mpg}".format(**row))

"""
Explanation: print a list of the results
End of explanation
"""


import pandas as pd
df = pd.read_sql_query("SELECT * from results", conn)
print(df.head())

"""
Explanation: import into pandas
End of explanation
"""


