[go: up one dir, main page]
Scribd
Upload
7 views29 pages

Step 8 Chapter1

Uploaded by

Ajay Nain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views29 pages

Step 8 Chapter1

Uploaded by

Ajay Nain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 29

Introduction to

Databases
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N

Jason Myers
Co-Author of Essential SQLAlchemy and
So ware Engineer
A database consists of tables

INTRODUCTION TO DATABASES IN PYTHON


Table consist of columns and rows

INTRODUCTION TO DATABASES IN PYTHON


Table consist of columns and rows

INTRODUCTION TO DATABASES IN PYTHON


Tables can be related

INTRODUCTION TO DATABASES IN PYTHON


Let's practice!
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N
Connecting to your
database
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N

Jason Myers
Co-Author of Essential SQLAlchemy and
So ware 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

Microso 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 nd the


database (and login, if necessary)

INTRODUCTION TO DATABASES IN PYTHON


A word on connection strings

Driver + Dialect

INTRODUCTION TO DATABASES IN PYTHON


A word on connection strings

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
Re ection 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!
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N
Introduction to SQL
queries
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N

Jason Myers
Co-Author of Essential SQLAlchemy and
So ware Engineer
SQL Statements
Select, insert, update, and delete data

Create and alter data

INTRODUCTION TO DATABASES IN PYTHON


Basic SQL querying
SELECT column_name FROM table_name

SELECT pop2008 FROM People

SELECT * FROM People

INTRODUCTION TO DATABASES IN PYTHON


Basic SQL querying
from sqlalchemy import create_engine
engine = create_engine('sqlite:///census_nyc.sqlite')
connection = engine.connect()
stmt = 'SELECT * FROM people'
result_proxy = connection.execute(stmt)
results = result_proxy.fetchall()

INTRODUCTION TO DATABASES IN PYTHON


ResultProxy vs ResultSet
result_proxy = connection.execute(stmt)

results = result_proxy.fetchall()

result_proxy is a ResultProxy

results is a ResultSet

INTRODUCTION TO DATABASES IN PYTHON


Handling ResultSets
first_row = results[0]
print(first_row)

('Illinois', 'M', 0, 89600, 95012)

print(first_row.keys())

['state', 'sex', 'age', 'pop2000', 'pop2008']

print(first_row.state)

'Illinois'

INTRODUCTION TO DATABASES IN PYTHON


SQLAlchemy to build queries
Provides a Pythonic way to build SQL statements

Hides di erences between backend database types

INTRODUCTION TO DATABASES IN PYTHON


SQLAlchemy querying
from sqlalchemy import Table, MetaData
metadata = MetaData()
census = Table('census', metadata, autoload=True,
autoload_with=engine)
stmt = select([census])
results = connection.execute(stmt).fetchall()

INTRODUCTION TO DATABASES IN PYTHON


SQLAlchemy select statement
Requires a list of one or more Tables or Columns

Using a table will select all the columns in it

stmt = select([census])
print(stmt)

'SELECT * from CENSUS'

INTRODUCTION TO DATABASES IN PYTHON


Let's practice!
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N
Congratulations!
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N

Jason Myers
Co-Author of Essential SQLAlchemy and
So ware Engineer
You already...
Know about the relational model

Can make basic SQL queries

INTRODUCTION TO DATABASES IN PYTHON


Coming up next...
Beef up your SQL querying skills

Learn how to extract all types of useful information from your


databases using SQLAlchemy

Learn how to create and write to relational databases

Deep dive into the US census dataset!

INTRODUCTION TO DATABASES IN PYTHON


See you in the next
chapter!
I N T R O D U C T I O N T O D ATA B A S E S I N P Y T H O N

You might also like