sql_alchemy example

db.sql

-- tables
-- Table: client
CREATE TABLE client (
    id int  NOT NULL,
    full_name varchar(255)  NOT NULL,
    email varchar(255)  NOT NULL,
    CONSTRAINT client_pk PRIMARY KEY (id)
);
 
-- Table: purchase
CREATE TABLE purchase (
    id int  NOT NULL,
    purchase_no char(12)  NOT NULL,
    client_id int  NOT NULL,
    CONSTRAINT purchase_pk PRIMARY KEY (id)
);
 
-- foreign keys
-- Reference:  client_purchase (table: purchase)
 
 
ALTER TABLE purchase ADD CONSTRAINT client_purchase 
    FOREIGN KEY (client_id)
    REFERENCES client (id)
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE 
;

how _to_use

#################### init #####################
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///')
 from output import *
 
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)

####################### Insert ###########################
>>> s = session()
>>> c = Client(full_name=’client1’, email=’client1@gmail.com’)
>>> p = Purchase(purchase_no=u’abcd123’, client_id=c.id)
>>> s.add(c)
>>> s.add(p)
>>> s.commit()


##################### Query ############################
>>> c = s.query(Client).filter(Client.full_name == 'client1').one()
 
>>>  c.full_name
 u'client1'
 
>>> c.email
 u'client1@gmail.com'
 
>>> p = s.query(Purchase).filter(Purchase.client_id == c.id).one()
>>> p.purchase_no
 u'abcd123'

model.py

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, BigInteger,String, ForeignKey, Unicode, Binary, LargeBinary, Time, DateTime, Date, Text, Boolean
from sqlalchemy.orm import relationship, backref, deferred
from sqlalchemy.orm import sessionmaker
 
 
Base = declarative_base()
 
class Purchase (Base):
    __tablename__ = "purchase"
    id = Column('id', Integer, primary_key = True)
    purchase_no = Column('purchase_no', Unicode)
    client_id = Column('client_id', Integer, ForeignKey('client.id'))
 
    client = relationship('Client', foreign_keys=client_id)
 
class Client (Base):
    __tablename__ = "client"
    id = Column('id', Integer, primary_key = True)
    full_name = Column('full_name', Unicode)
    email = Column('email', Unicode)