Connecting to your database

Introduction to Databases in Python

Jason Myers

Co-Author of Essential SQLAlchemy and Software Engineer

Meet SQLAlchemy

  • Two main pieces
    • Core (Relational Model focused)
    • ORM (User Data Model focused)
Introduction to Databases in Python

There are many types of databases

  • SQLite
  • PostgreSQL
  • MySQL
  • Microsoft SQL Server
  • Oracle SQL
  • Many more
Introduction to Databases in Python

Connecting to a database

from sqlalchemy import create_engine

engine = create_engine('sqlite:///census_nyc.sqlite')

connection = engine.connect()
  • Engine: common interface to the database from SQLAlchemy
  • Connection string: All the details required to find the database (and login, if necessary)
Introduction to Databases in Python

A word on connection strings

driverdialect.png

        Driver + Dialect

Introduction to Databases in Python

A word on connection strings

filename.png

                                                                                               Filename

Introduction to Databases in Python

What's in your database?

Before querying your database, you'll want to know what is in it: what the tables are, for example:

from sqlalchemy import create_engine

engine = create_engine('sqlite:///census_nyc.sqlite')

print(engine.table_names())

['census', 'state_fact']
Introduction to Databases in Python

Reflection

Reflection reads database and builds SQLAlchemy Table objects

from sqlalchemy import MetaData, Table

metadata = MetaData()

census = Table('census', metadata, autoload=True,
  autoload_with=engine)

print(repr(census))

Table('census', MetaData(bind=None), Column('state', VARCHAR(
length=30), table=<census>), Column('sex', VARCHAR(length=1),
table=<census>), Column('age', INTEGER(), table=<census>),
Column('pop2000', INTEGER(), table=<census>), Column('pop2008',
INTEGER(), table=<census>), schema=None)
Introduction to Databases in Python

Let's practice!

Introduction to Databases in Python

Preparing Video For Download...