<jupyter_start><jupyter_text>`echo=True` for debugging SQL<jupyter_code>url = 'sqlite:///datasets/Chinook_Sqlite.sqlite'
engine = create_engine(url, echo=True)
engine.table_names()<jupyter_output>2019-06-19 12:17:07,888 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-06-19 12:17:07,889 INFO sqlalchemy.engine.base.Engine ()
2019-06-19 12:17:07,892 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-06-19 12:17:07,892 INFO sqlalchemy.engine.base.Engine ()
2019-06-19 12:17:07,894 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
2019-06-19 12:17:07,895 INFO sqlalchemy.engine.base.Engine ()<jupyter_text>--- ReflectionReflection reads database and builds SQLAlchemy Table objects<jupyter_code>from sqlalchemy import MetaData, Table
metadata = MetaData()<jupyter_output><empty_output><jupyter_text>SQLAlchemy Table object for existing table `Track`<jupyter_code>track = Table('Track', metadata, autoload=True, autoload_with=engine)
track<jupyter_output><empty_output><jupyter_text>Column names for a table<jupyter_code>track.columns.keys()<jupyter_output><empty_output><jupyter_text>SQL Queries<jupyter_code>stmt = 'select * from Track'
connection = engine.connect()
result_proxy = connection.execute(stmt)
results = result_proxy.fetchall()
first_row = results[0]
first_row
first_row.keys()
print(first_row.Name, first_row.UnitPrice)
first_row['Composer']
first_row[6]
engine.table_names()
metadata = MetaData()
album = Table('Album', metadata, autoload=True, autoload_with=engine)
artist = Table('Artist', metadata, autoload=True, autoload_with=engine)
customer = Table('Customer', metadata, autoload=True, autoload_with=engine)
employee = Table('Employee', metadata, autoload=True, autoload_with=engine)
genre = Table('Genre', metadata, autoload=True, autoload_with=engine)
invoice = Table('Invoice', metadata, autoload=True, autoload_with=engine)
invoiceline = Table('InvoiceLine', metadata, autoload=True, autoload_with=engine)
mediatype = Table('MediaType', metadata, autoload=True, autoload_with=engine)
playlist = Table('Playlist', metadata, autoload=True, autoload_with=engine)
playlisttrack = Table('PlaylistTrack', metadata, autoload=True, autoload_with=engine)
track = Table('Track', metadata, autoload=True, autoload_with=engine)
from sqlalchemy import select
stmt1 = select([album.c.Title])
stmt1
print(stmt1)
res = engine.execute(stmt1).fetchall()
res<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12450903377847604, 'fraction_numerical': 0.04477611940298507, 'mean_word_length': 9.107142857142858, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '381172', 'license': 'Apache-2.0', 'n_tokens_mistral': 961, 'n_tokens_neox': 870, 'n_words': 174, 'path': 'sqlalchemy.ipynb', 'repo_name': 'suru33/py-notebooks', 'size': '2546'}