<jupyter_start><jupyter_text>SQLAlchemy — Python Tutorial[Source](https://towardsdatascience.com/sqlalchemy-python-tutorial-79a577141a91)Other References* [What are Object Relational Mappers (ORMs)](https://www.fullstackpython.com/object-relational-mappers-orms.html)* [SQLAlchemy Basics](https://leportella.com/english/2019/01/10/sqlalchemy-basics-tutorial.html)We often encounter data as Relational Databases. To work with them we generally would need to write raw SQL queries, pass them to the database engine and parse the returned results as a normal array of records.SQLAlchemy provides a nice “Pythonic” way of interacting with databases. So rather than dealing with the differences between specific dialects of traditional SQL such as MySQL or PostgreSQL or Oracle, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data.<jupyter_code># Installing The Package
!pip install sqlalchemy<jupyter_output><empty_output><jupyter_text>Connecting to a databaseTo start interacting with the database we first we need to establish a connection.```pythonimport sqlalchemy as dbengine = db.create_engine('dialect+driver://user:pass@host:port/db')``` Database connection examples[Source](https://docs.sqlalchemy.org/en/13/core/engines.htmlpostgresql) PostgreSQL```python defaultengine = create_engine('postgresql://scott:tiger@localhost/mydatabase') psycopg2engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase') pg8000engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')``` MySQL```python defaultengine = create_engine('mysql://scott:tiger@localhost/foo') mysqlclient (a maintained fork of MySQL-Python)engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo') PyMySQLengine = create_engine('mysql+pymysql://scott:tiger@localhost/foo')``` Oracle```pythonengine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')``` Microsoft SQL Server```python pyodbcengine = create_engine('mssql+pyodbc://scott:tiger@mydsn') pymssqlengine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')``` SQLite```python sqlite:/// where  is relative:engine = create_engine('sqlite:///foo.db') Unix/Mac - 4 initial slashes in totalengine = create_engine('sqlite:////absolute/path/to/foo.db') Windowsengine = create_engine('sqlite:///C:\\path\\to\\foo.db') Windows alternative using raw stringengine = create_engine(r'sqlite:///C:\path\to\foo.db') To use a SQLite :memory: database, specify an empty URL:engine = create_engine('sqlite://')```  Viewing Table DetailsSQLAlchemy can be used to automatically load tables from a database using something called reflection. Reflection is the process of reading the database and building the metadata based on that information.<jupyter_code>import sqlalchemy as db
engine = db.create_engine('sqlite:///census.sqlite')
connection = engine.connect()
metadata = db.MetaData()
census = db.Table('census', metadata, autoload=True, autoload_with=engine)
# Print the column names
print(census.columns.keys())
# Print full table metadata
print(repr(metadata.tables['census']))<jupyter_output><empty_output><jupyter_text>Querying```Table``` and ```MetaData``` have already been imported. The metadata is available as ```metadata```.<jupyter_code>import sqlalchemy as db
engine = db.create_engine('sqlite:///census.sqlite')
connection = engine.connect()
metadata = db.MetaData()
census = db.Table('census', metadata, autoload=True, autoload_with=engine)
#Equivalent to 'SELECT * FROM census'
query = db.select([census])<jupyter_output><empty_output><jupyter_text>**ResultProxy**: The object returned by the ```.execute()``` method. It can be used in a variety of ways to get the data returned by the query.<jupyter_code>ResultProxy = connection.execute(query)<jupyter_output><empty_output><jupyter_text>**ResultSet**: The actual data asked for in the query when using a fetch method such as ```.fetchall()``` on a ResultProxy.<jupyter_code>ResultSet = ResultProxy.fetchall()
ResultSet[:3]<jupyter_output><empty_output><jupyter_text>Dealing with Large ResultSetWe use ```.fetchmany()``` to load optimal no of rows and overcome memory issues in case of large datasets.<jupyter_code>ResultProxy = connection.execute(query)
# ResultSet = ResultProxy.fetchall()
flag = True
while flag:
    print('*** new fetch')
    partial_results = ResultProxy.fetchmany(5)
    if(partial_results == []): 
        flag = False
    for result in partial_results:
        print('\t', result)

#     print(partial_results)
ResultProxy.close()<jupyter_output><empty_output><jupyter_text>Convert to DataFrame<jupyter_code>import pandas as pd
df = pd.DataFrame(ResultSet)
df.columns = ResultSet[0].keys()
df.head()<jupyter_output><empty_output><jupyter_text>Filtering dataLets see some examples of raw SQLite Queries and queries using SQLAlchemy.  where**SQL**```SQLSELECT * FROM census WHERE sex = F```<jupyter_code># SQLAlchemy
db.select([census]).where(census.columns.sex == 'F')<jupyter_output><empty_output><jupyter_text>in**SQL**```SQLSELECT state, sexFROM censusWHERE state IN (Texas, New York)```<jupyter_code># SQLAlchemy
db.select([census.columns.state, census.columns.sex]) \
    .where(census.columns.state.in_(['Texas', 'New York']))<jupyter_output><empty_output><jupyter_text>and, or, not**SQL**```SQLSELECT * FROM censusWHERE state = 'California' AND NOT sex = 'M'```<jupyter_code># SQLAlchemy
db.select([census]) \
    .where(db.and_(census.columns.state == 'California', 
                   census.columns.sex != 'M'))<jupyter_output><empty_output><jupyter_text>order by**SQL**```SQLSELECT * FROM censusORDER BY State DESC, pop2000```<jupyter_code># SQLAlchemy
db.select([census]).order_by(
        db.desc(census.columns.state), 
        census.columns.pop2000)<jupyter_output><empty_output><jupyter_text>functionsother functions include ```avg```, ```count```, ```min```, ```max```**SQL**```SQLSELECT SUM(pop2008)FROM census```<jupyter_code># SQLAlchemy
query = db.select([db.func.sum(census.columns.pop2008)])
ResultSet = connection.execute(query).fetchall()
pd.DataFrame(ResultSet)<jupyter_output><empty_output><jupyter_text>group by**SQL**```SQLSELECT SUM(pop2008) as pop2008, sexFROM censusGROUP BY sex```<jupyter_code># SQLAlchemy
query = db.select([db.func.sum(census.columns.pop2008).label('pop2008'), 
           census.columns.sex]).group_by(census.columns.sex)
ResultSet = connection.execute(query).fetchall()
pd.DataFrame(ResultSet)<jupyter_output><empty_output><jupyter_text>distinct**SQL**```SQLSELECT DISTINCT stateFROM census```<jupyter_code># SQLAlchemy
query = db.select([census.columns.state.distinct()])
ResultSet = connection.execute(query).fetchall()
pd.DataFrame(ResultSet)<jupyter_output><empty_output><jupyter_text>case & castThe ```case()``` expression accepts a list of conditions to match and the column to return if the condition matches, followed by an ```else_``` if none of the conditions match.```cast()``` function to convert an expression to a particular type<jupyter_code>engine = db.create_engine('sqlite:///census.sqlite')
connection = engine.connect()
metadata = db.MetaData()
census = db.Table('census', metadata, autoload=True, autoload_with=engine)
female_pop = db.func.sum(
    db.case([(census.columns.sex == 'F', census.columns.pop2000)],
            else_=0))
ResultSet = connection.execute(female_pop).fetchall()
pd.DataFrame(ResultSet)
total_pop = db.cast(db.func.sum(census.columns.pop2000), db.Float)
query = db.select([female_pop/total_pop * 100])
ResultSet = connection.execute(query).fetchall()
pd.DataFrame(ResultSet)
result = connection.execute(query).scalar()
print(result)<jupyter_output><empty_output><jupyter_text>We use ```.scalar``` to the result when the result contains only single value  joinsIf you have two tables that already have an established relationship, you can automatically use that relationship by just adding the columns we want from each table to the select statement.```pythonselect([census.columns.pop2008, state_fact.columns.abbreviation])```<jupyter_code>engine = db.create_engine('sqlite:///census.sqlite')
connection = engine.connect()
metadata = db.MetaData()

census = db.Table('census', metadata, autoload=True, autoload_with=engine)
state_fact = db.Table('state_fact', metadata, autoload=True, autoload_with=engine)
# Automatic Join
query = db.select([census.columns.pop2008, state_fact.columns.abbreviation])
results = connection.execute(query).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df.head(5)
# Manual Join
query = db.select([census, state_fact])
query = query.select_from(census.join(state_fact, census.columns.state == state_fact.columns.name))
results = connection.execute(query).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df.head(5)<jupyter_output><empty_output><jupyter_text>Creating and Inserting Data into TablesBy passing the database which is not present, to the engine then sqlalchemy automatically creates a new database.  Creating Database and Table<jupyter_code># delete the test database
import os
test_db_name = 'test.sqlite'

if os.path.exists(test_db_name): 
    os.remove(test_db_name)
engine = db.create_engine(f'sqlite:///{test_db_name}') #Create test.sqlite automatically
connection = engine.connect()
metadata = db.MetaData()

emp = db.Table('emp', metadata,
              db.Column('Id', db.Integer()),
              db.Column('name', db.String(255), nullable=False),
              db.Column('salary', db.Float(), default=100.0),
              db.Column('active', db.Boolean(), default=True)
              )

metadata.create_all(engine) #Creates the table<jupyter_output><empty_output><jupyter_text>Inserting Data<jupyter_code>#Inserting record one by one
query = db.insert(emp).values(Id=1, name='naveen', salary=60000.00, active=True) 
ResultProxy = connection.execute(query)
#Inserting many records at ones
query = db.insert(emp) 
values_list = [{'Id':'2', 'name':'ram', 'salary':80000, 'active':False},
               {'Id':'3', 'name':'ramesh', 'salary':70000, 'active':True}]
ResultProxy = connection.execute(query, values_list)
results = connection.execute(db.select([emp])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df.head(4)<jupyter_output><empty_output><jupyter_text>Updating data in Databases```pythondb.update(table_name).values(attribute = new_value).where(condition)```<jupyter_code>engine = db.create_engine(f'sqlite:///{test_db_name}')
metadata = db.MetaData()
connection = engine.connect()
emp = db.Table('emp', metadata, autoload=True, autoload_with=engine)
results = connection.execute(db.select([emp])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df.head(4)
# Build a statement to update the salary to 100000
query = db.update(emp).values(salary = 100000)
query = query.where(emp.columns.Id == 1)
results = connection.execute(query)
results = connection.execute(db.select([emp])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df.head(4)<jupyter_output><empty_output><jupyter_text>Delete Table```pythondb.delete(table_name).where(condition)```<jupyter_code>engine = db.create_engine(f'sqlite:///{test_db_name}')
metadata = db.MetaData()
connection = engine.connect()
emp = db.Table('emp', metadata, autoload=True, autoload_with=engine)
results = connection.execute(db.select([emp])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df.head(4)
# Build a statement to delete where salary < 100000
query = db.delete(emp)
query = query.where(emp.columns.salary < 100000)
results = connection.execute(query)
results = connection.execute(db.select([emp])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df.head(4)<jupyter_output><empty_output><jupyter_text>Dropping a Table```pythontable_name.drop(engine) drops a single tablemetadata.drop_all(engine) drops all the tables in the database```<jupyter_code>engine = db.create_engine(f'sqlite:///{test_db_name}')
metadata = db.MetaData()
connection = engine.connect()
# drop a table
emp = db.Table('emp', metadata, autoload=True, autoload_with=engine)
emp.drop(engine)
# drop all tables
metadata.drop_all(engine)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1416842789790278, 'fraction_numerical': 0.013168590473093806, 'mean_word_length': 6.72316384180791, 'pattern_counts': {'":': 0, '<': 87, '<?xml version=': 0, '>': 85, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '662449', 'license': 'MIT', 'n_tokens_mistral': 4071, 'n_tokens_neox': 3867, 'n_words': 849, 'path': 'lectures/Week 01 - Language basics, Generating Data, Storing Data/03 - Getting Data from a Database.ipynb', 'repo_name': 'BrianKolowitz/data-focused-python', 'size': '12302'}