0x01 Intraduction

SQLAlchemy is a flexible library for SQL ORM of python.It can help you work with high-level SQL witch is constructed automatically for you,as well as automated persistence of Python objects.

0x02 Object relational Mapping

SQL databases behave less like object collections the more size and performance start to matter; object collections behave less like tables and rows the more abstraction starts to matter. ORM aims to accommodate both of these principles.
关系型数据库起源于数学建模，而面向对象编程是基于软件工程的基本原则发展起来的，两者的基础理论存在显著差异，因此伴随着对象的发展和数据库数据的增大，两者联系的矛盾愈加突出，因此对象关系映射正是为了解决这一矛盾应运而生的技术。

0x03 QuickStart

  1. Connecting
    To connect we use create_engine():

    1
    2
    3
    #connect to mysql
    In [1]: from sqlalchemy import create_engine
    In [2]: engine = create_engine('mysql://root@localhost/sqlalchemy')

    The Engine when first returned by create_engine(), has not actually tried to connect to the database yet till the first time it is asked to perform a task against the database.

  2. Declare a Mapping
    We use Declarative to create classes that include directives to describe the actual database table they will be mapped to.
    我们使用Declarative实现对数据表的描述并完成其与对象的映射关系。

    1
    2
    In [3]: from sqlalchemy.ext.declarative import declarative_base
    In [4]: Base = declarative_base()

  3. Create Object

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    >>> from sqlalchemy import Column, Integer, String
    >>> 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)

    Let’s see the table attribution.

    1
    2
    In [8]: User.__table__
    Out[8]: Table('users', MetaData(bind=None), Column('id', Integer(), table=<users>, primary_key=True, nullable=False), Column('name', String(), table=<users>), Column('fullname', String(), table=<users>), Column('password', String(), table=<users>), schema=None)

    Then create table

    1
    2
    In [9]: Base.metadata.create_all(engine)
    CompileError                              Traceback (most recent call last)

    It returned error named CompileError because of Mysql should constrain the length of String.
    Modify our code, add length after “String”.

    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
    >>> from sqlalchemy import Column, Integer, String
    >>> class User(Base):
    ...     __tablename__ = 'users'
    ...
    ...     id = Column(Integer, primary_key=True)
    ...     name = Column(String(50))
    ...     fullname = Column(String(50))
    ...     password = Column(String(50))
    ...
    ...     def __repr__(self):
    ...        return "<User(name='%s', fullname='%s', password='%s')>" % (
    ...                             self.name, self.fullname, self.password)

    #mysql
    mysql> show create table users;
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | users | CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(50) DEFAULT NULL,
    	  `fullname` varchar(50) DEFAULT NULL,
    	    `password` varchar(50) DEFAULT NULL,
    		  PRIMARY KEY (`id`)
    		  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    		  +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    		  1 row in set (0.00 sec)

  4. Create an Instance of Mapped Class
    With mapping complete, let’s create a User object and insert it into our database.

    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
    >>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
    >>> from sqlalchemy.orm import sessionmaker
    >>> Session = sessionmaker(bind=engine)
    >>> session = Session()
    >>> session.new
    	IdentitySet([<User(name='ed', fullname='ED Jones', password='123456')>])
    >>> session.commit()

    #mysql table rows
    mysql> select * from users;
    +----+------+----------+----------+
    | id | name | fullname | password |
    +----+------+----------+----------+
    |  1 | ed   | ED Jones | 123456   |
    +----+------+----------+----------+
    1 row in set (0.00 sec)

    # add more users
    >>> 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()

    #mysql table rows
    mysql> select * from users;
    +----+------+----------+----------+
    | id | name | fullname | password |
    +----+------+----------+----------+
    |  1 | ed   | ED Jones | 123456   |
    |  2 | san  | zhangsan | passsan  |
    |  3 | si   | zhangsi  | passsi   |
    |  4 | wu   | zhangwu  | passwu   |
    +----+------+----------+----------+
    4 rows in set (0.00 sec)

  5. Rollback
    Before session commit executing, we could use session.rollback() look up for changed data.

  6. Deleting
    Let’s try to delete ‘zhangsan’. Using sessiono.delete(object).

  7. Quering
    Example

    1
    2
    >>> query = session.query(User).filter(User.name.liek("%ed')).order_by(User.id)
    >>> query.all() //query.first()

0x04 The end

More high-level usage refers to Document of SQLAlchemy