sqlalchemy 系列教程一

sqlalchemy 系列教程一

sqlalchemy概述

sqlalchemy 作为python 主流的ORM 框架,用这个框架有一段时间, 写个博客记录一下.
ORM 与 SQLAlchemy 简介
ORM 全称 Object Relational Mapping, 叫对象关系映射。简单的说，ORM 将数据库中的表与面向对象语言中的类建立了一种对应关系。这样，我们要操作数据库，数据库中的表或者表中的一条记录就可以直接通过操作类或者类实例来完成。

创建一个base 基类

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@Time    : 2018/11/12 11:26
@File    : base.py
@Author  : frank.chang@shoufuyou.com


session
"""
from datetime import datetime

from sqlalchemy import create_engine, DateTime
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from secure import XINYONGFEI_BI_URL

engine = create_engine(XINYONGFEI_BI_URL, pool_size=10, pool_recycle=7200,
                       pool_pre_ping=True, encoding='utf-8')

_Base = declarative_base(bind=engine, name='xinyongfei_fisher')


session_factory = sessionmaker()
session_factory.configure(bind=engine)

session = session_factory()


class Base(_Base):
    __abstract__ = True
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }

    create_time = Column(DateTime, nullable=False, default=datetime.now)


if __name__ == '__main__':
    # Base.metadata.create_all(engine, checkfirst=True)  # 创建表结构
    # Base.metadata.create_all(engine)  # 创建表结构

    pass

XINYONGFEI_BI_URL
连接字符串的配置

XINYONGFEI_BI_URL = 'mysql+pymysql://root:xxxxxx#@ip:port/db_name?charset=utf8'

create_engine 参考文档

形式如下

The string form of the URL is dialect[+driver]😕/user:password@host/dbname[?key=value…], where dialect is a database name such as mysql, oracle, postgresql, etc., and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle, etc. Alternatively, the URL can be an instance of URL.

创建一个子类

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@Time    : 2018/11/12 11:14
@File    : creat_table.py
@Author  : frank.chang@shoufuyou.com


https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#creating-a-session
"""

from base import Base, session
from sqlalchemy import Column, Integer, String
from base import engine

from model.book import Book
from model.gift import Gift
from model.wish import Wish
from model.user import User


class TUser(Base):
    __tablename__ = 'TUser'

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(length=64), comment='姓名')
    mobile = Column(String(length=64), comment='手机号')
    password = Column(String(length=64), comment='密码')

    def __repr__(self):
        return "<User(id='%s', name='%s', mobile='%s', password='%s')>" % \
               (self.id,
                self.name, self.mobile, self.password)


def create_table():
    # 创建表结构
    Base.metadata.create_all(engine,
                             tables=[TUser.__table__, Gift.__table__, Wish.__table__, User.__table__, Book.__table__])

代码结构图:

生成一张表

if __name__ == '__main__':

   
    # Base.metadata.drop_all()
    Base.metadata.create_all()
   
    pass

调用 Base.metadata.create_all() 就会生成一张表,只要继承Base的类,都会生成一张表.
如果没有生成很有可能, 没有把类导入创建表的脚本里面.
或者向 create_all(engine,tables=[])

注意,tables 是个列表,列表内容是 类名.table ,

插入一些数据.

来插入一些数据测试

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@Time    : 2018/11/12 20:45
@File    : insert_table.py
@Author  : frank.chang@shoufuyou.com
"""

"""

为了将User对象存入数据库，我们调用Session 的add()函数
 


"""

from base import session
from model.creat_table import TUser

if __name__ == '__main__':

    for user_id in range(10):
        ed_user = TUser(name='frank' + str(user_id), mobile='12349213' + str(user_id),
                        password='edspassword' + str(user_id))
        session.add(ed_user)

    session.commit()

    session.close()

通过session.add() ,之后 要 session.commit() ,这样就可以把 数据写到数据库里面了.

查询一些数据

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@Time    : 2018/11/13 19:09
@File    : query_data.py
@Author  : frank.chang@shoufuyou.com
"""

from base import session
from model.creat_table import TUser
from datetime import datetime



if __name__ == '__main__':
   
    print(session.query(TUser).filter_by(name='frank25'))
    print(session.query(TUser).filter_by(name='frank25').all())

结果如下:

SELECT `TUser`.create_time AS `TUser_create_time`, `TUser`.id AS `TUser_id`, `TUser`.name AS `TUser_name`, `TUser`.mobile AS `TUser_mobile`, `TUser`.password AS `TUser_password` 
FROM `TUser` 
WHERE `TUser`.name = %(name_1)s
[<User(id='26', name='frank25', mobile='1234921325', password='edspassword25')>]

filter_by() 查询返回一个query 对象, 如果要查询 要加上 .all(),filter_by 相当于sql 里面的where 条件.

其实query 可以直接用切片取值

    query = session.query(TUser).filter_by()
    print(query[0:2])

结果如下:
[<User(id='1', name='frank0', mobile='123492130', password='edspassword0')>, <User(id='2', name='frank1', mobile='123492131', password='edspassword1')>]

参考文档
https://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine