SQLAlchemy Using python to interact with SQL databases

SQLAlchemy Using python to interact with SQL databases Eowyn Baughman, Oct 10 2017

Py. Data & SQL • Python is popular in data science • SQL is widely used to interact with databases– e. g. Post. GRES, Oracle • • • Query (SELECT) Conditionals (WHERE) Sorting (GROUP BY) Combining data (JOIN) Update the database (INSERT) • SQLAlchemy is a Python package to natively interact with databases within Python

SQLAlchemy Tutorial Step-by-step tutorial With Examples: http: //www. rmunn. com/sqlalchemy-tutorial/tutorial. html “You almost never need to write SQL by hand. ” Goes much more into detail, explores more advanced topics Full Documentation: http: //www. sqlalchemy. org/docs/index. myt

SQLAlchemy: Establish dataset • db = create_engine(‘adatabase. db’) • • Open connection to database The resulting object has methods specific to the type of database • Create an object that knows what the columns of the data are, what types of data they contain, etc. Used to re-create analogous data structures in Python • metadata = Bound. Meta. Data(db) • • users = Table('users', metadata, autoload=True) • Figure out automagically what the table in the database looks like and load it for Python to work with

SQLAlchemy: Building SQL Statements • • • Main idea: Create “SQL statement objects” instead of writing SQL Then call its “execute” method that belongs to the object Table objects have methods like: users. select() to pull all or some of the data to your local table users. insert() to update data in your local table Both of which can accept conditional statements • Can also uses join(table 1, table 2). select() or outerjoin(table 1, table 2). select() to work on multiple tables

Mapping objects to the database • Saving your data back to the original database 1. Create empty classes to serve as data classes 2. “map” your data tables onto the empty data classes, which then take on the correct column names and types 3. Create a “session” object to keep track of data classes 4. Select (load) data from database into data classes 5. Modify them however you like 6. “Flush” the session to push changes to the database

Example of Mapping from sqlalchemy import * db = create_engine('sqlite: ///joindemo. db’) metadata = Bound. Meta. Data(db) users = Table('users', metadata, autoload=True) emails = Table('emails', metadata, autoload=True) # These are the empty classes that will become our data classes class User(object): pass class Email(object): pass usermapper = mapper(User, users) emailmapper = mapper(Email, emails) session = create_session() mary = session. query(User). selectfirst(users. c. name=='Mary’) #you are forced to be explicit about which table, and #what kind of object, you are selecting data from. “In #the face of ambiguity, refuse the temptation to #guess” mary. age += 1 session. flush()