Python3操作数据库SqlAlchemy

1.数据库信息

Database	Versions	User	Password	Host
mysql	5.5.52-MariaDB MariaDB Server	root	12345678	3306

2.pymysql

  安装pymysql库

$ pip install pymysql

  连接数据库

conn = pymysql.connect(host='127.0.0.1', user='root', password='12345678', database='test', port=3306)
# 必要参数，返回连接
MariaDB [test]> show processlist;
+----+------+---------------------+------+---------+------+-------+------------------+----------+
| Id | User | Host                | db   | Command | Time | State | Info             | Progress |
+----+------+---------------------+------+---------+------+-------+------------------+----------+
|  6 | root | 192.168.195.1:52288 | NULL | Sleep   |  258 |       | NULL             |    0.000 |
|  7 | root | 192.168.195.1:52289 | test | Sleep   |  258 |       | NULL             |    0.000 |
|  8 | root | localhost           | test | Query   |    0 | NULL  | show processlist |    0.000 |
| 10 | root | localhost:49860     | test | Sleep   |   37 |       | NULL             |    0.000 |

  游标操作

>>> cur = conn.cursor() # 获取游标
# 游标执行sql非查询性操作
>>> cur.execute('''insert into user(name, age) value('exe', 1)''') 
1
>>> conn.commit() # 提交事务，在数据库生效
MariaDB [test]> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lock |   20 |
|  3 | exe  |    1 |
+----+------+------+
2 rows in set (0.00 sec)
# 游标执行查询性操作
>>> cur.execute('select * from user') 
2
>>> cur.fetchall() # 以元组的元祖只返回一次全部查询结果
((1, 'lock', 20), (3, 'exe', 1))
>>> cur.execute('select * from user')
2
>>> cur.fetchmany(1) # 以元祖的元祖返回指定行数查询结果，返回完为止
((1, 'lock', 20),)
>>> cur.fetchmany(1)
((3, 'exe', 1),)
>>> cur.execute('select * from user')
2
>>> cur.fetchone() # 以元祖返回一行查询结果，返回完为止
(1, 'lock', 20)
>>> cur.fetchone()
(3, 'exe', 1)
# 返回带列名的字典
>>> cur.close()
>>> from pymysql.cursors import DictCursor
>>> cur = conn.cursor(cursor=DictCursor)
>>> cur.execute('select * from user') # for update排他锁
2
>>> cur.fetchall()
[{'id': 1, 'name': 'lock', 'age': 20}, {'id': 3, 'name': 'exe', 'age': 1}]

  参数化查询

cur.execute(query, args) # 参数化查询，避免sql注入，提高查询效率
>>> def get_juveniles(age=18):
...   query = '''select * from user where age < %s for update'''
...   cur.execute(query, (age,))
...   return cur.fetchall()
... 
>>> get_juveniles()
[{'id': 3, 'name': 'exe', 'age': 1}]
>>> get_juveniles('18 or 1 = 1')
/home/Lock/.pyenv/versions/Lock/lib/python3.5/site-packages/pymysql/cursors.py:323: Warning: (1292, "Truncated incorrect DOUBLE value: '18 or 1 = 1'")
  self._do_get_result()
[{'id': 3, 'name': 'exe', 'age': 1}]
# 结果忽略出错参数
>>> def get_juveniles2(age=18):
...   query = '''select * from user where age < {} for update'''.format(age)
...   cur.execute(query)
...   return cur.fetchall()
... 
>>> get_juveniles2()
[{'id': 3, 'name': 'exe', 'age': 1}]
>>> get_juveniles2('18 or 1 =  1')
[{'id': 1, 'name': 'lock', 'age': 20}, {'id': 3, 'name': 'exe', 'age': 1}]
# 用户传递参数，出现sql注入
>>> def get_juveniles3(age=18):
...   query = '''select * from user where age < %(age)s for update'''
...   cur.execute(query, {'age':age})
...   return cur.fetchall()
... 
>>> get_juveniles3(10)
[{'id': 3, 'name': 'exe', 'age': 1}]
# 关键字参数化查询

  with块

>>> with conn.cursor() as cur2:
...   cur2.execute('''update user set age=18 where name='exe' ''')
... 
1
>>> cur2.execute('select * from user')
pymysql.err.ProgrammingError: Cursor closed
# 自动提交事务，并关闭游标cur2 不会关闭cur
PS：关闭的游标是with的cur不是外部的cur
>>> with conn as cur3:
...   with cur3:
...     cur3.execute('select * from user')
... 
4
# 自动提交事务，第一层不关闭cur3，第二层关闭cur3

  连接池

import  pymysql
import threading
from queue import Queue

class ConnectionPool:
    def __init__(self, size, *args, **kwargs):
        self._pool = Queue(size)
        self.args = args
        self.kwargs = kwargs
        for x in range(size):
            self._pool.put(self._connect())
        self.local = threading.local()

    def _connect(self):
        return pymysql.connect(*self.args, **self.kwargs)

    def _close(self, conn):
        conn.close()

    def __enter__(self):
        cur =  getattr(self.local, 'cursor', None)
        if cur is None:
            setattr(self.local, 'cursor', self.get().cursor())
        return self.local.cursor
# threadind.local来实现with块
    def __exit__(self, *args):
        self.local.cursor.connection.commit()
        self.return_resource(self.local.cursor.connection)
        self.local.cursor.close()
        self.local.cursor = None

    def get(self):
        return self._pool.get()

    def return_resource(self, conn):
        self._pool.put(conn)

cp = ConnectionPool(10, host='127.0.0.1', user='root', password='12345678', database='test', port=3306)


with cp as cur:
    cur.execute('select * from user')
print(cur.fetchall())

3. ROM实现

  描述器实现字段

class Field:
    def __init__(self, name, column=None, primary_key=False, unique=False, index=False, nullable=True, default=None):
        self.name = name # 存储名
        self.column = column # 列名
        self.primary_key = primary_key # 以下修饰
        self.unique = unique
        self.index = index
        self.nulladble = nullable
        self.default = default

    def validate(self, value) -> bool:
        raise NotImplemented

    def __get__(self, instance, owner):
        if instance is None:
            return self
        return instance.__dict__[self.name]

    def __set__(self, instance, value):
        self.validate(value)
        instance.__dict__[self.name] = value
# 基列对象

class IntField(Field):
    def __init__(self, name, column=None, primary_key=False, unique=False, index=False, nullable=True, default=None, auto_increasement=False):
        super().__init__(name, column, primary_key, unique, index, nullable, default) # 初始父构造
        self.auto_increasement = auto_increasement # 增加构造

    def validate(self, value):
        if not self.nulladble and value is None:
            raise TypeError('{}<{}> required'.format(self.name, self.column))
        if value is None:
            return
        if not isinstance(value, int):
            raise TypeError('{}<{}> must be int but {}'.format(self.column, self.name, type(value)))

class StringField(Field):
    def __init__(self, name, column=None, primary_key=False, unique=False, index=False, nullable=True, default=None,
                 length=45):
        super().__init__(name, column, primary_key, unique, index, nullable, default)
        self.length = length

    def validate(self, value):
        if value is None:
            if self.nulladble:
                return
            else:
                raise TypeError('{}<{}> required'.format(self.name, self.column))
        if not isinstance(value, str):
            raise TypeError('{}<{}> must be string but {}'.format(self.column, self.name, type(value)))
        if len(value) > self.length:
            raise ValueError('{}<{}> too long'.format(self.name , self.column))
# 只有一个name对应的value值，其他都是描述
# class User:
#     id = IntField(name='id', column='id', primary_key=True, auto_increasement=True)
#     name = StringField(name='name', column='name', nullable=False, unique=True, length=64)
#     age = IntField(name='age', column='age', nullable=False)
# 
#     def __init__(self, name, age):
#         self.id = id
#         self.name = name
#         self.age = age
# 
#     def save(self, session):
#         query = '''insert into `user`(`id`, `name`, `age`) value(%s, %s, %s)'''
#         with session:
#             session.execute(query, self.id, self.name, self.age)
# 
# 
# class Session:
#     def __init__(self, conn):
#         self.conn = conn
#         self.cur = None
# 
#     def __enter__(self):
#         self.cur = self.conn.cursor()
#         return self
# 
#     def __exit__(self, *args):
#         self.conn.commit()
# 
#     def execute(self, query, *args):
#         self.cur.execute(query, args)
# 应用代码

  通用化save

class Model:
    def save(self):
        fields = {}
        for name, field in self.__class__.__dict__.items():
            if isinstance(field, Field):
                fields[name] = field # 保存表所有列名|列对象
        keys = []
        values = []
        for name, value in self.__dict__.items(): # 所有赋值列
            if name in fields.keys():
                keys.append('`{}`'.format(name)) # 保持列名
                values.append(value) # 保存列值
        query = '''insert into {} ({}) value({})'''.format(self.__class__.__table__, ','.join(keys), ','.join(['%'] * len(keys)))
        print(query)
# 被表对象继承
class User(Model):
    __table__ = 'user'

    id = IntField(name='id', column='id', primary_key=True, auto_increasement=True)
    name = StringField(name='name', column='name', nullable=False, unique=True, length=64)
    age = IntField(name='age', column='age', nullable=False)

    def __init__(self,id, name, age):
        self.id = id
        self.name = name
        self.age = age


User(1, 'lock', 16).save()
<<< insert into user (`id`,`name`,`age`) value(%,%,%)

  再通用化save

class ModelMeta(type):
    def __new__(cls, name, bases, attrs):
        if '__table__' not in attrs.keys():
            attrs['__table__'] = name
        mapping = {}
        primary_key = []
        for k, v in attrs.items():
            if isinstance(v, Field):
                v.name = k
                if v.column is None:
                    v.column = k
                mapping[k] = v
                if v.primary_key:
                    primary_key.append(v)
        attrs['__mapping__'] = mapping
        attrs['__primary_key__'] = primary_key
        return super().__new__(cls, name, bases, attrs)
# 在元类统一获取表所有字段

   删除name column都可以在基类里得赋予，不用再给

class Field:
    def __init__(self, primary_key=False, unique=False, index=False, nullable=True, default=None):
        self.name = None
        self.column = None
        self.primary_key = primary_key
        self.unique = unique
        self.index = index
        self.nulladble = nullable
        self.default = default

    def validate(self, value) -> bool:
        raise NotImplemented

    def __get__(self, instance, owner):
        if instance is None:
            return self
        return instance.__dict__[self.name]

    def __set__(self, instance, value):
        self.validate(value)
        instance.__dict__[self.name] = value

class IntField(Field):
    def __init__(self, primary_key=False, unique=False, index=False, nullable=True, default=None, auto_increasement=False):
        super().__init__( primary_key, unique, index, nullable, default)
        self.auto_increasement = auto_increasement

    def validate(self, value):
        if not self.nulladble and value is None:
            raise TypeError('{}<{}> required'.format(self.name, self.column))
        if value is None:
            return
        if not isinstance(value, int):
            raise TypeError('{}<{}> must be int but {}'.format(self.column, self.name, type(value)))

class StringField(Field):
    def __init__(self, primary_key=False, unique=False, index=False, nullable=True, default=None,
                 length=45):
        super().__init__( primary_key, unique, index, nullable, default)
        self.length = length

    def validate(self, value):
        if value is None:
            if self.nulladble:
                return
            else:
                raise TypeError('{}<{}> required'.format(self.name, self.column))
        if not isinstance(value, str):
            raise TypeError('{}<{}> must be string but {}'.format(self.column, self.name, type(value)))
        if len(value) > self.length:
            raise ValueError('{}<{}> too long'.format(self.name , self.column))


class ModelMeta(type):
    def __new__(cls, name, bases, attrs):
        if '__table__' not in attrs.keys():
            attrs['__table__'] = name
        mapping = {}
        primary_key = []
        for k, v in attrs.items():
            if isinstance(v, Field):
                v.name = k
                if v.column is None:
                    v.column = k
                mapping[k] = v
                if v.primary_key:
                    primary_key.append(v)
        attrs['__mapping__'] = mapping
        attrs['__primary_key__'] = primary_key
        return super().__new__(cls, name, bases, attrs)


class Model(metaclass=ModelMeta):
    pass

class User(Model):
    __table__ = 'user'

    id = IntField( primary_key=True, auto_increasement=True)
    name = StringField( nullable=False, unique=True, length=64)
    age = IntField( nullable=False)

print(User.__mapping__)

<<<': <__main__.IntField object at 0x7f4d25472780>, 'name': <__main__.StringField object at 0x7f4d254727f0>, 'age': <__main__.IntField object at 0x7f4d25472828>}

  构造映射

class Engine():
    def __init__(self, *args, **kwargs):
        '''connection'''
        self.conn = pymysql.connect(*args, **kwargs)
        self.cur = self.conn.cursor()
        self.query = ''
    
    def save(self, instance:Model) -> None:
        '''save instance to db'''
        fields = {'`{}`'.format(k): v for k, v in instance.__dict__.items() if k in instance.__class__.__mapping__.keys()}
        keys = []
        values = []
        for name, value in self.__dict__.items():
            if name in fields.keys():
                keys.append('`{}`'.format(name))
                values.append(value)
        query = '''insert into {} ({}) value({})'''.format(self.__class__.__table__, ','.join(keys), ','.join(['%'] * len(keys)))
        print(query)

4. SQLAchemy的使用

>>> sqlalchemy.__version__
'1.1.14'
import sqlalchemy
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:12345678@127.0.0.1:3306/test')
engine = create_engine('mysql+pymysql://root:12345678@127.0.0.1:3306/test', echo=True)
# 创建连接，uri资源索引，echo打印SQL执行

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
# 创建Model基类

from sqlalchemy import Column, Integer, String
# 创建表需要的字段，int类型和string类型

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    name = Column(String(64), unique=True, nullable=False)
    age = Column(Integer, nullable=True)
    
    def __str__(self): # 不是必须，用于调试
        return 'User(is={}, name={}, age={})'.format(self.id, self.name, self.age)
# 描述类到表的映射

Base.metadata.create_all(engine) 
# 在SQL实现描述，创建Base的所有表结构，在engine连接的数据库
CREATE TABLE user (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(64) NOT NULL, 
	age INTEGER, 
	PRIMARY KEY (id), 
	UNIQUE (name)
)

Base.metadata.drop_all(engine)
# 生产不建议

Base.metadata.create_all(engine)# 创建表结构
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine) # 创建会话类，线程安全的，只有一个类
session = Session() # 实例会话，线程不安全，有多个session。每个add对应一个session，对每个add插入记录
#对于同个add重复执行commit，后面的会更新前面记录（id不会浪费）
user = User() # 创建一行
user.name = 'lock'
user.age = 18
session.add(user) # 插入一行
user.name = 'charlotte'
user.age = '20' # 参数化操作，只有在commit的时候 才会赋值
session.commit() # 提交会话，执行sql；一旦commit发生异常，后续的commit都不能正常执行
MariaDB [test]> select * from user;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | charlotte |   20 |
+----+------+------+
1 row in set (0.01 sec)
try:
    session.commit()
except Exception as e:
    session.rollback() # 异常时候，撤销。（浪费id）
    raise e

session.delete(user) # 只能对session这个Session add过的对象删除
# 操作表

q = session.query(User) # 对User表查询，返回可迭代对象，迭代时执行sql
for u in q:
    print(u)
<<<User(is=1, name=lock, age=18)
<<<User(is=1, name=lock, age=20)
nq = q.filter(User.age < 20) # 增加where，多个filter是AND
for u in nq:
    print(u)
<<<User(is=1, name=lock, age=18)
from sqlalchemy import or_, and_, not_
nq2 = q.filter(or_(User.age = 20, User.age < 20)) # 增加OR
nq3 = q.filter(or_(User.age = 20, User.age < 20)).order_by(User.age) # 排序，再接 多条件排序
nq3 = q.filter(or_(User.age = 20, User.age < 20)).order_by(User.age.desc()) # 倒排
for u in session.query(User).limit(5) # 分页
  print(u)
for u in session.query(User).offset(5).limit(5) # 开始行,分页
  print(u)
session.query(User).count() # 计数
session.query(User).first() # 返回第一个对象
session.query(User).delete() # delete by query
session.commit()

from sqlalchemy import func # 聚合函数
session.query(func.count(User.id)).first()
session.query(User.age, func.count(User.id)).group_by().all() 分组
# 查询表

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Author(Base):
    __tablename__ = 'author'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

    posts = relationship('Post', foreign_keys='[Post.author_id]') # 其他表的外键''

    def __repr__(self):
        return 'Author<id={}, name={}>'.format(self.id, self.name)

    def __str__(self):
        return self.__repr__()


class Post(Base):
    __tablename__ = 'post'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(128), nullable=False, index=True)
    context = Column(String(8096), nullable=False)
    author_id = Column(Integer, ForeignKey('author.id'), nullable=False) # 先创建外键

    author = relationship('Author', foreign_keys=[author_id]) # 再创建靠外键的关系，本身表

    def __repr__(self):
        return 'Post<id={}, title={}>'.format(self.id, self.title)

    def __str__(self):
        return self.__repr__()


Base.metadata.create_all(engine)

a = Author()
a.name = 'lock'

p = Post()
p.author = a
p.title = 'test_title'
p.context = 'test context.'

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()
session.add(p)
session.commit()

print(session.query(Post, Author).filter(Post.author_id == Author.id).filter(Author.name == 'lock').all())
<<< [(Post<id=1, title=test_title>, Author<id=3, name=lock>)]
print(session.query(Post).join(Author, Post.author_id == Author.id).filter(Author.name == 'lock').all())
<<< [Post<id=1, title=test_title>]
# 联合查询

官方SqlAlchemy文档

转载于:https://my.oschina.net/charlock/blog/1538559