sqlalchemy学习(一)


sqlalchemy

系统的学一下sqlalchemyA,不过没有中文文档,只能用我的渣英文能力慢慢看原文档了

数据库连接

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

echo为True输出日志

各种数据库连接方式

dialect+driver://username:password@host:port/database
  1. Postgresql

    # default
    engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
    # psycopg2
    engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
    # pg8000
    engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
    
  2. MySQL

    # default
    engine = create_engine('mysql://scott:tiger@localhost/foo')
    # mysql-python
    engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
    # MySQL-connector-python
    engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
    # OurSQL
    engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')
    
  3. Oracle

    engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
    engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')
    
  4. SQLite

    # sqlite://<nohostname>/<path>
    # where <path> is relative:
    engine = create_engine('sqlite:///foo.db')
    #Unix/Mac - 4 initial slashes in total
    engine = create_engine('sqlite:////absolute/path/to/foo.db')
    #Windows
    engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
    #Windows alternative using raw string
    engine = create_engine(r'sqlite:///C:\path\to\foo.db')
    # To use a SQLite :memory: database, specify an empty URL:
    engine = create_engine('sqlite://')
    

声明样式

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

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)

样式参数说明

  说明
**\_tablename\_\_** 表名

字段类型及说明

字段类型及文档

  1. Generic Types

    字段类型 说明
    BigInteger 大数
    Boolean(createconstraint=True, name=None, _createevents=True) 布尔
    Data  
    DateTime(timezone=False) 时间
    Enum(*enums, **kw) 枚举
    Float(precision=None, asdecimal=False, decimalreturnscale=None, **kwargs) 浮点型
    Integer 整型
    Interval(native=True, secondprecision=None, dayprecision=None) 时间间隔
    LargeBinary(length=None) 二进制大对象
    MatchType(createconstraint=True, name=None, _createevents=True)  
    Numeric(precision=None, scale=None, decimalreturnscale=None, asdecimal=True)  
    PickleType(protocol=2, pickler=None, comparator=None)  
    SchemaType(name=None, schema=None, metadata=None, inheritschema=False, quote=None, _createevents=True)  
    SmallInteger 小整型
    String(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False) 字符串
    Text(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False) 文本对象
    Time(timezone=False) 时间
    Unicode(length=None, **kwargs)  
    UnicodeText(length=None, **kwargs)  

  2. SQL Standard Types

    字段类型 说明
    BIGINT  
    BINARY(length=None)  
    BLOB(length=None)  
    BOOLEAN(createconstraint=True, name=None, _createevents=True)  
    CHAR(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)  
    CLOB(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)  
    DATE  
    DATETIME(timezone=False)  
    DECIMAL(precision=None, scale=None, decimalreturnscale=None, asdecimal=True)  
    FLOAT(precision=None, asdecimal=False, decimalreturnscale=None, **kwargs)  
    INT  
    INTEGER  
    NCHAR(length=None, **kwargs)  
    NVARCHAR(length=None, **kwargs)  
    NUMERIC(precision=None, scale=None, decimalreturnscale=None, asdecimal=True)¶  
    REAL(precision=None, asdecimal=False, decimalreturnscale=None, **kwargs)  
    SMALLINT  
    TEXT(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False  
    TIME(timezone=False)  
    TIMESTAMP(timezone=False)  
    VARBINARY(length=None)  
    VARCHAR(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)  

创建表

Base.metadata.create_all(engine)

为表创建实例

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'

创建session

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

你也可以先创建Session,在创建应用前

>>> Session = sessionmaker()

当你创建了应用,可以这样配置

>>> Session.configure(bind=engine)  # once engine is available

插入和更新数据

插入

插入一条数据

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> session.add(ed_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')])

更新

>>> ed_user.password = 'f8s7ccs'

查看

>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])
>>> session.new  # doctest: +SKIP
IdentitySet([<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()

Comments !