Database Programming

  • Need to graduate from SQL magic (which is not Python) to Python programming:
    • to issue queries
      • incorporating Python variables
    • to obtain and process results
    • to incorporate into data frames
    • to create table and database schema
    • to populate database tables with rows of data

SQL Alchemy

https://www.sqlalchemy.org/

  • Provides a common "front end" for use in our Python programs that works across different database management systems, including MySQL, SQLite, PostreSQL, Oracle, and many others.
  • Different possible levels of interaction
    • raw queries
    • SQL query "objects"
    • Database Object Model

Logical Connection Arguments

  • protocol scheme for kind of database
  • lower level driver
  • for remote: server machine and "port"/application
  • for remote: authorization credentials (user and password)
  • for local: file system path for database
  • for multiple-database capable system: default database

Remote Example

In [34]:
protocol = "mysql"
driver = "mysqlconnector"
server = "hadoop.mathsci.denison.edu"
port = 3306
user = "studen_j1"
password = "studen_j1"
database = "book"

template = "{}+{}://{}:{}@{}:{}/{}"
cstring1 = template.format(protocol, driver, user, password, server, port, database)
print(cstring1)
cstring1 = f"{protocol}+{driver}://{user}:{password}@{server}:{port}/{database}"
print(cstring1)

mysql+mysqlconnector://studen_j1:studen_j1@hadoop.mathsci.denison.edu:3306/book
mysql+mysqlconnector://studen_j1:studen_j1@hadoop.mathsci.denison.edu:3306/book
In [32]:
database = "school"
In [33]:
print(cstring1)

mysql+mysqlconnector://studen_j1:studen_j1@hadoop.mathsci.denison.edu:3306/book

Local Example

In [9]:
import os.path

protocol = "sqlite"
driver = "pysqlite"
dbdir = "../../dbfiles"
database = "book"

cstring2 = f"{protocol}+{driver}:///{os.path.join(dbdir, database + '.db')}"
print(cstring2)

sqlite+pysqlite:///../../dbfiles/book.db

Connecting and Closing

Explicit Close

In [12]:
import sqlalchemy as sa
In [12]:
engine = sa.create_engine(cstring)
connection = engine.connect()
In [13]:
try:
    connection.close()
except: 
    pass
del engine

Using with/Implicit Cose

In [14]:
engine = sa.create_engine(cstring)
with engine.connect() as connection:
    # Perform database requests and process replies
    pass
del engine

Basic Query Execution

In [16]:
engine = sa.create_engine(cstring)
connection = engine.connect()
In [17]:
query = """
SELECT *  FROM indicators0
"""
In [18]:
result_proxy = connection.execute(query)
In [19]:
type(result_proxy)
Out[19]:
sqlalchemy.engine.result.ResultProxy
In [20]:
result_list = result_proxy.fetchall()
print(result_list)

[('CHN', 1386.4, 12143.5, 76.4, 1469.88), ('FRA', 66.87, 2586.29, 82.5, 69.02), ('GBR', 66.06, 2637.87, 81.2, 79.1), ('IND', 1338.66, 2652.55, 68.8, 1168.9), ('USA', 325.15, 19485.4, 78.5, 391.6)]
In [21]:
for record in result_list:
    print(record)

('CHN', 1386.4, 12143.5, 76.4, 1469.88)
('FRA', 66.87, 2586.29, 82.5, 69.02)
('GBR', 66.06, 2637.87, 81.2, 79.1)
('IND', 1338.66, 2652.55, 68.8, 1168.9)
('USA', 325.15, 19485.4, 78.5, 391.6)
In [22]:
firstrecord = result_list[2]
In [23]:
firstrecord
Out[23]:
('GBR', 66.06, 2637.87, 81.2, 79.1)
In [24]:
firstrecord[0]
Out[24]:
'GBR'
In [25]:
firstrecord['code']
Out[25]:
'GBR'
In [27]:
list(firstrecord.keys())
Out[27]:
['code', 'pop', 'gdp', 'life', 'cell']
In [28]:
result_proxy.keys()
Out[28]:
['code', 'pop', 'gdp', 'life', 'cell']
In [ ]: