Python的ORM框架SQLAlchemy

jjytr

API例子

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84

import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker print(sqlalchemy.__version__) # # examples of connection http://docs.sqlalchemy.org/en/re ... chemy.create_engine engine = create_engine('sqlite:///foo.db', echo=True) Base = declarative_base() class User(Base):     __tablename__ = 'users'     id = Column(Integer, primary_key=True)     name = Column(String)     fullname = Column(String)     password = Column(String)     def __repr__(self):        return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password) Base.metadata.create_all(engine) ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') print(ed_user) Session = sessionmaker(bind=engine) session = Session() session.add(ed_user) our_user = session.query(User).filter_by(name='ed').first() # SELECT * FROM users WHERE name="ed" LIMIT 1; print our_user session.add_all([      User(name='wendy', fullname='Wendy Williams', password='foobar'),      User(name='mary', fullname='Mary Contrary', password='xxg527'),      User(name='fred', fullname='Fred Flinstone', password='blah')]) session.commit() print(session.query(User).filter_by(name='ed').first()) print(session.query(User).all()) for row in session.query(User).order_by(User.id):     print(row) for row in session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])):     print(row) # for row in session.query(User).filter(~User.name.in_(['ed', 'wendy', 'jack'])): #     print(row) # print(session.query(User).filter(User.name == 'ed').count()) # # from sqlalchemy import and_, or_ # for row in session.query(User).filter(and_(User.name == 'ed', User.fullname == 'Ed Jones')): #     print(row) # for row in session.query(User).filter(or_(User.name == 'ed', User.name == 'wendy')): #     print(row) # # from sqlalchemy import ForeignKey # from sqlalchemy.orm import relationship, backref # # class Address(Base): #     __tablename__ = 'addresses' #     id = Column(Integer, primary_key=True) #     email_address = Column(String, nullable=False) #     user_id = Column(Integer, ForeignKey('users.id')) # #     user = relationship("User", backref=backref('addresses', order_by=id)) # #     def __repr__(self): #         return "<Address(email_address='%s')>" % self.email_address # # Base.metadata.create_all(engine) # # # jack = User(name='jack', fullname='Jack Bean', password='gjffdd') # jack.addresses = [ #                 Address(email_address='jack@google.com'), #                 Address(email_address='j25@yahoo.com')] # session.add(jack) # session.commit() # # for u, a in session.query(User, Address).\ #                     filter(User.id==Address.user_id).\ #                     filter(Address.email_address=='jack@google.com').\ #                     all(): #     print u, a

SQLAlchemy Core 相当于底层实现，很少使用

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

import sqlalchemy print(sqlalchemy.__version__) from sqlalchemy import create_engine from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey engine = create_engine('sqlite:///foo.db', echo=True) metadata = MetaData() users = Table('users', metadata,     Column('id', Integer, primary_key=True),     Column('name', String),     Column('fullname', String), ) addresses = Table('addresses', metadata,   Column('id', Integer, primary_key=True),   Column('user_id', None, ForeignKey('users.id')),   Column('email_address', String, nullable=False) ) metadata.create_all(engine) conn = engine.connect() # conn.execute(users.insert(), [dict(name='jack', fullname='Jack Jones'), #                               dict(name='wendy', fullname='Wendy Williams')]) # conn.execute(addresses.insert(), [ #    {'user_id': 1, 'email_address' : 'jack@yahoo.com'}, #    {'user_id': 1, 'email_address' : 'jack@msn.com'}, #    {'user_id': 2, 'email_address' : 'www@www.org'}, #    {'user_id': 2, 'email_address' : 'wendy@aol.com'}, # ]) from sqlalchemy.sql import select # s = select([users]) # result = conn.execute(s) # for row in result: #     print(row) # s = select([users, addresses]).where(users.c.id == addresses.c.user_id) # for row in conn.execute(s): #     print row from sqlalchemy.sql import text s = text(     "SELECT users.fullname || ', ' || addresses.email_address AS title "         "FROM users, addresses "         "WHERE users.id = addresses.user_id "         "AND users.name BETWEEN :x AND :y "         "AND (addresses.email_address LIKE :e1 "             "OR addresses.email_address LIKE :e2)") print(conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall())

__repr__是python类的专用方法:你可以通过定义类的__repr__方法来控制你的对象在被repr函数调用的时候返回的内容。

Python 有办法将任意值转为字符串：将它传入repr() 或str() 函数。

函数str() 用于将值转化为适于人阅读的形式，而repr() 转化为供解释器读取的形式（如果没有等价的语法，则会发生SyntaxError 异常） 某对象没有适于人阅读的解释形式的话， str() 会返回与repr()等同的值。很多类型，诸如数值或链表、字典这样的结构，针对各函数都有着统一的解读方式。字符串和浮点数，有着独特的解读方式。

repr函数用来取得对象的规范字符串表示。反引号（也称转换符）可以完成相同的功能。注意，在大多数时候有eval(repr(object)) == object。