WGBPnRe8

1#A SQLAlchemy Cheat Sheet
2###Introduction
3
4##Basic Models
5One model is used to describe one database table. For example:
6
7from sqlalchemy.ext.declarative import declarative_base
8from sqlalchemy.orm import scoped_session,sessionmaker
9from zope.sqlalchemy import ZopeTransactionExtension
10from sqlalchemy import (
11    Column,
12    Integer,
13    String,
14    Boolean,
15    ForeignKey,
16    DateTime,
17    Sequence,
18    Float
19)
20import datetime
21
22DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
23Base = declarative_base()
24
25class Book(Base):  #<------------------------- 
26    __tablename__  = "books"    #matches the name of the actual database table
27    id             = Column(Integer,Sequence('book_seq'),primary_key=True) # plays nice with all major database engines
28    name           = Column(String(50))                                    # string column need lengths
29    author_id      = Column(Integer,ForeignKey('authors.id'))              # assumes there is a table in the database called 'authors' that has an 'id' column
30    price          = Column(Float)
31    date_added     = Column(DateTime, default=datetime.datetime.now)       # defaults can be specified as functions
32    promote        = Column(Boolean,default=False)                         #     or as values
33Queries and Interactions
34Selecting and Filtering
35
36fetch everything
37lBooks = DBSession.query(Book)  #returns a Query object. 
38for oBook in lBooks:
39    print oBook.name
40
41#simple filters
42lBooks = DBSession.query(Book).filter_by(author_id=1) #returns all the books for a specific author
43
44#more complex filters
45lBooks = DBSession.query(Book).filter(Book.price<20) #returns all the books with price <20. Note we use filter, not filter_by
46
47#filters can be combined
48lBooks = DBSession.query(Book).filter_by(author_id=1).filter(Book.price<20) #all books by a specific author, with price<20
49
50#logical operations can be used in filters
51from sqlalchemy import or_
52lBooks = DBSession.query(Book).filter(or_(Book.price<20,promote==True)) # returns all books  that cost less than 20 OR are being promoted
53
54#ordering
55from sqlalchemy import desc
56DBSession.query(Book).order_by(Book.price) #get all books ordered by price
57DBSession.query(Book).order_by(desc(Book.price)) #get all books ordered by price descending
58
59#other useful things
60DBSession.query(Book).count() #returns the number of books
61DBSession.query(Book).offset(5) #offset the result by 5
62DBSession.query(Book).limit(5) # return at most 5 books
63DBSession.query(Book).first() #return the first book only or None
64DBSession.query(Book).get(8) #return the Book with primary key = 8, or None 
65Relationships
66Relationships between SQL tables are described in terms of foreign key relationships. From the example, the books table has a foreign key field pointing to the id field of the authors table. SQLAlchemy makes leveraging and examining those relationships pretty straight forward.
67
68One to many relationships
69
70Assume we are keeping track of the books of various authors. One author can have many books.
71
72class Book(Base):
73    __tablename__  = "books"    #matches the name of the actual database table
74    id             = Column(Integer,Sequence('book_seq'),primary_key=True) 
75    name           = Column(String(50))                                    
76    author_id      = Column(Integer,ForeignKey('authors.id'))              
77    author = relationship("Author",backref="books")             # <-----------------------
78    
79class Author(Base):
80    __tablename__  = "books"    #matches the name of the actual database table
81    id             = Column(Integer,Sequence('book_seq'),primary_key=True) 
82    name           = Column(String(50))
83The marked line configures the relationship between the models. Note that "Author" is a string. It doesn't need to be, it can also be a class. Using a string here removes the possibility of certain NameErrors. Note that the relationship is configured in both directions in one line. A book's author is accessable via the author attribute, and an author's books are accessable via the author's books attribute.
84
85Here are a few ways you can make use of the relationship once it is configured:
86
87oBook = DBSession.query(Book).filter_by(name="Harry Potter and the methods of rationality").first()
88oAuthor = oBook.author   # oAuthor is now an Author instance. oAuthor.id == oBook.author_id
89
90#it works the other way as well
91oAuthor = DBSession.query(Author).filter_by(name="Orsan Scott Card")
92for oBook in oAuthor.books:
93    print oBook.name
94
95#adding a new book
96oNewBook = Book()
97oBook.name = "Ender's Game"
98oBook.author = oAuthor
99
100#adding a new book in a different way...
101oNewBook = Book()
102oBook.name = "Ender's Shadow"
103oAuthor.books.append(oBook)
104One to one relationships
105
106class Parent(Base):
107    __tablename__ = 'parent'
108    id = ColumnColumn(Integer,Sequence('p_seq'),primary_key=True) 
109    child_id = Column(Integer, ForeignKey('child.id'))
110    child = relationship("Child", backref=backref("parent", uselist=False)) # <------
111
112class Child(Base):
113    __tablename__ = 'child'
114    id = Column(Integer,Sequence('c_seq'),primary_key=True) 
115Note that the line that configures the relationship has an expression instead of just a string for the backref argument. If the string "parent" was used then it would be a normal many to one relationship. We can make use of this like so:
116
117oChild = DBSession.query(Child).get(1)
118oParent = oChild.parent
119
120oParent2 = Parent()
121oParent.child = Child()
122Many to many relationships
123
124A many to many relationship requires an extra table to create mappings between lines. There are two ways of doing this:
125
126First, just using models:
127
128class Category(Base):
129    __tablename__ = 'categories'
130    id = Column(Integer,Sequence('cat_seq'),primary_key=True) 
131    name = Column(String(20))
132
133class Product(Base):
134    __tablename__ = 'products'
135    id = Column(Integer,Sequence('prod_seq'),primary_key=True) 
136    name = Column(String(20))
137    
138class Map(Base):
139    __tablename__ = 'map'
140    id = Column(Integer,Sequence('map_seq'),primary_key=True) 
141    cat_id = Column(Integer,ForeignKey('categories.id'))
142    prod_id = Column(Integer,ForeignKey('products.id'))
143Here you can specify relationships on the Map class. The benefit of this approach is that you can instantiate the Map class, this is useful if you want to interact with Map objects in any non-trivial way.
144
145This next approach is better if your map table is only a map table and requires no complex interactions:
146
147map_table = Table('maps', Base.metadata,
148    Column('cat_id', Integer, ForeignKey('categories.id')),
149    Column('prod_id', Integer, ForeignKey('products.id'))
150)
151
152class Category(Base):
153    __tablename__ = 'categories'
154    id = Column(Integer,Sequence('cat_seq'),primary_key=True) 
155    name = Column(String(20))
156
157    products = relationship("Product",
158                    secondary=map_table,   # you can also use the string name of the table, "maps", as the secondary
159                    backref="categories")
160
161class Product(Base):
162    __tablename__ = 'products'
163    id = Column(Integer,Sequence('prod_seq'),primary_key=True) 
164    name = Column(String(20))
165
166You can make use of the relationship like this:
167
168#construct a category and add some products to it
169oCat = Category()
170oCat.name = "Books"
171
172oProduct = Product()
173oProduct.name = "Ender's Game - Orsan Scott Card"
174oCat.products.append(oProduct)
175
176oProduct = Product()
177oProduct.name = "Harry Potter and the methods of Rationality"
178oProduct.categories.append(oCat)
179
180# interact with products from an existing category
181for oProduct in oCat.products:
182    print oProduct.name
183    
184#interact with categories of an existing product
185oProduct = DBSession.query(Product).filter_by(name="")
186for oCat in oProduct.categories:
187    print oCat.name
188    
189Self referential relationships
190
191Sometimes you have a table with a foreign key pointing at the same table. For example, say we have a bunch of nodes in a directed tree. A node can have many child nodes but at most one parent
192
193class TreeNode(Base):
194    __tablename__ = 'nodes'
195    id = Column(Integer,Sequence('node_seq'),primary_key=True) 
196    parent_id = Column(Integer,ForeignKey('nodes.id'))
197    name = Column(String(20))
198
199    children = relationship("TreeNode",
200                backref=backref('parent', remote_side=[id])
201            )
202You can make use of this relationship like any many to one relationship:
203
204#fetch the root node (assume there is one node with no parents)
205oRootNode = DBSession.query(TreeNode).filter_by(parent_id=None).first()
206
207#interact with children of existing node
208for oChild in oRootNode.children:
209    print oChild.name
210
211#create new relationships
212
213oParent = TreeNode()
214oParent.name = "parent"
215oRootNode.children.append(oParent)
216
217oChild = TreeNode()
218oChild.name = "Child"
219oChild.parent = oParent
220Multiple relationships with the same table
221
222class WikiPost(Base):
223    __tablename__ = 'posts'
224    id = Column(Integer,Sequence('post_seq'),primary_key=True) 
225    name = Column(String(20))
226    author_id = Column(Integer,ForeignKey('users.id'))
227    editor_id = Column(Integer,ForeignKey('users.id'))
228
229    editor = relationship("User", primaryjoin = "WikiPost.editor_id == User.id",backref="edited_posts")
230    author = relationship("User", primaryjoin = "WikiPost.author_id == User.id",backref="authored_posts")
231
232class User(Base):
233    __tablename__ = 'users'
234    id = Column(Integer,Sequence('usr_seq'),primary_key=True) 
235    name = Column(String(20))
236You can interact with this just like two many to one relationships.
237
238oAuthor = DBSession.query(User).filter_by(name="Sheena O'Connell")
239
240#an author writes a post
241oPost = WikiPost()
242oPost.name = "Sqlalchemy Cheat Sheet"
243oPost.author = oAuthor
244
245#later on another user edits it
246oEditor = DBSession.query(User).filter_by(name="Yi-Jirr Chen")
247oEditor.edited_posts.append(oPost)
248
249#interact with existing relationships
250for oPost in oAuthor.authored_posts:
251    print oPost.name
252    
253for oPost in oEditor.edited_posts:
254    print oPost.name
255Engine Configuration
256Connection Strings
257
258#the general form of a connection string:
259`dialect+driver://username:password@host:port/database` 
260
261#SQLITE:
262'sqlite:///:memory:' #store everything in memory, data is lost when program exits
263'sqlite:////absolute/path/to/project.db')  #Unix/Mac
264'sqlite:///C:\\path\\to\\project.db' #Windows
265r'sqlite:///C:\path\to\project.db' #Windows alternative
266
267#PostgreSQL
268
269'postgresql://user:pass@localhost/mydatabase'
270'postgresql+psycopg2://user:pass@localhost/mydatabase'
271'postgresql+pg8000://user:pass@localhost/mydatabase'
272
273#Oracle
274'oracle://user:pass@127.0.0.1:1521/sidname'
275'oracle+cx_oracle://user:pass@tnsname'
276
277#Microsoft SQL Server
278'mssql+pyodbc://user:pass@mydsn'
279'mssql+pymssql://user:pass@hostname:port/dbname'
280Engine, Session and Base
281
282#set up the engine
283engine = create_engine(sConnectionString, echo=True)   #echo=True makes the sql commands issued by sqlalchemy get output to the console, useful for debugging
284
285#bind the dbsession to the engine 
286DBSession.configure(bind=engine)
287
288#now you can interact with the database if it exists
289
290#import all your models then execute this to create any tables that don't yet exist. This does not handle migrations
291Base.metadata.create_all(engine)