sql连接处理

序言

数据存储是一个很重要的话题，小到C里面的struct，到os的一个个数据表，大到一个个数据库软件乃至单纯提供数据存储和访问服务的集群，提供数据的快速访问、持久化维护、崩坏数据的恢复，数据的加密维护等可算一个系列工种了。想说它是个很大的话题，落在python中，其实也就很多都是用，这里关注那么几个数据库在python中的对口api，用几个例子来进行说明，嗯，这里的都是那么几个sql相关数据库。

一、细小便捷的sqlite

一种嵌入式的数据库，数据库就一个文件而已，相对来说是非常轻量的，对于业务量不大的功能性网站或者客户端的部分适合保存在本机的数据都是比较合适的一个选择。针对sql系列的数据库，大多有着多个单条关系数据聚集成表，多个表又集合成一个个数据库，而针对数据库的各种高级语言的api操作中也有着这样的概念：数据库的连接是connection，连接到数据库后，进行操作的是游标cursor，通过cursor执行操作，针对已有各种数据库操作接口的python，要进行连接就需要提供数据库的驱动，而sqlite驱动内置在python标准库中，所以不用另外安装sqlite3数据库。操作一下：

>>> import sqlite3
>>>
>>>
>>> conn = sqlite3.connect('book.db')
>>> cursor = conn.cursor()
>>> dir(conn)
['DataError', 'DatabaseError', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError', 'NotSupportedError', 'OperationalError', 'ProgrammingError', 'Warning', '__call__', '__class__', '__delattr__', '__dir__', '__doc__', '__enter__', '__eq__', '__exit__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'backup', 'close', 'commit', 'create_aggregate', 'create_collation', 'create_function', 'cursor', 'enable_load_extension', 'execute', 'executemany', 'executescript', 'in_transaction', 'interrupt', 'isolation_level', 'iterdump', 'load_extension', 'rollback', 'row_factory', 'set_authorizer', 'set_progress_handler', 'set_trace_callback', 'text_factory', 'total_changes']
>>> dir(cursor)
['__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__next__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'arraysize', 'close', 'connection', 'description', 'execute', 'executemany', 'executescript', 'fetchall', 'fetchmany', 'fetchone', 'lastrowid', 'row_factory', 'rowcount', 'setinputsizes', 'setoutputsize']
>>> cursor.execute('create table books (id varchar(20) primary key, name varchar(20), price double)')
<sqlite3.Cursor object at 0x00000168917DBEC0>
>>> cursor.execute('insert into books (id, name, price) values (\'1\', \'GO语言编程之旅\', 80.9)')
<sqlite3.Cursor object at 0x00000168917DBEC0>
>>> cursor.rowcount
1
>>> conn.commit()
>>> cursor.execute('select * from books')
<sqlite3.Cursor object at 0x00000168917DBEC0>
>>> books = cursor.fetchall()
>>> books
[('1', 'GO语言编程之旅', 80.9)]
>>> cursor.close()
>>> conn.close()
>>>

如上面所示，先连接本地sqlite数据库(嗯，那么轻量的我见过的都是本地的)，然后获取游标，然后使用游标的execute接口来执行sql语句，除此以外还可以用executescript脚本，看接口名是这么个意思，还么试过。

像这种sql数据库，就和sql语句撇不开关系，对于旧版本的sql数据库，各种sql语句大多是大写，然后某个版本开始吧，就大小写均可，好像微软的SQL server还是用着大写？不太确定了。

sql语句也有着独特语法，了解数据库先了解其数据类型是很重要的，不过大多都跑不了字符、字符串、浮点、布尔和整型还有NULL空值这几种，多的就算数据库特性了，mysql的事务就算一个。

sql语句常见的大概就是create创建、drop删除数据库、update修改、insert插入、select查看、delete删除，条件判断大概就是where定位，and/or等。。。。。。

最烦的就是需要硬编码sql语句的第三方库，golang的database/sql和python的sqlite3或者pymysql等。

拿sqlite来做个练习，就把上面的book数据库做大吧：

import sqlite3

# 本来想套两层的with上下文管理器的，没成功，不懂
with sqlite3.connect('book.db') as conn:
    cursor = conn.cursor()
    cursor.execute('create table books(id int, name varchar(20), price double)')
    cursor.execute(r"insert into books values (1, 'GO语言编程之旅', 80.9)")
    cursor.execute(r"insert into books values (2, 'python爬虫实战', 79.0)")
    cursor.execute(r"insert into books values (3, 'Java核心技术 卷I', 48.0)")
    cursor.execute(r"insert into books values (4, 'Java核心技术 卷II', 48.0)")
    conn.commit()

做大以后再修改修改：

import sqlite3


def conn(db_file):
    return sqlite3.connect(db_file)

def execute(db_file, sql):
    conn = conn(db_file)
    with conn.cursor() as cursor:
        cursor.execute(sql)
        cursor.commit()
    conn.close()

二、有事务的mysql

mysql的应用非常广泛，而且使用公司也多，对应的许多工作也对mysql有特定要求，尤其是中国，导致各种八股文大量生成。

首先要备好mysql数据库和python操作第三方库pymysql，确定一下可行性：

PS D:\Desktop> mysql -h 192.168.0.105 -ujam -p 
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| somedb             |
+--------------------+
3 rows in set (0.21 sec)

mysql>

如上是准备好的我的外部mysql服务，也配置了一个用来练习的somedb和配套的jam用户。pip install pymysql

看看配套函数

>>> import pymysql     
>>>
>>> conn = pymysql.connect(host='192.168.0.105', user='jam', password='abaaba', database='somedb')
>>> dir(conn) 
['DataError', 'DatabaseError', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError', 'NotSupportedError', 'OperationalError', 'ProgrammingError', 'Warning', '__class__', '__del__', '__delattr__', '__dict__', '__dir__', '__doc__', '__enter__', '__eq__', '__exit__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_affected_rows', '_auth_plugin_map', '_auth_plugin_name', '_binary_prefix', '_closed', '_connect_attrs', '_create_ssl_ctx', '_execute_command', '_force_close', '_get_auth_plugin_handler', '_get_server_information', '_local_infile', '_next_seq_id', '_process_auth', '_quote_bytes', '_read_bytes', '_read_ok_packet', '_read_packet', '_read_query_result', '_read_timeout', '_request_authentication', '_result', '_rfile', '_secure', '_send_autocommit_mode', '_sock', '_write_bytes', '_write_timeout', 'affected_rows', 'autocommit', 'autocommit_mode', 'begin', 'bind_address', 'character_set_name', 'charset', 'client_flag', 'close', 'commit', 'connect', 'connect_timeout', 'cursor', 'cursorclass', 'db', 'decoders', 'encoders', 'encoding', 'escape', 'escape_string', 'get_autocommit', 'get_host_info', 'get_proto_info', 'get_server_info', 'host', 'host_info', 'init_command', 'insert_id', 'kill', 'literal', 'max_allowed_packet', 'next_result', 'open', 'password', 'ping', 'port', 'protocol_version', 'query', 'rollback', 'salt', 'select_db', 'server_capabilities', 'server_charset', 'server_language', 'server_public_key', 'server_status', 'server_thread_id', 'server_version', 'set_charset', 'show_warnings', 'sql_mode', 'ssl', 'thread_id', 'unix_socket', 'use_unicode', 'user', 'write_packet']
>>> cursor = conn.cursor()
>>> dir(cursor) 
['DataError', 'DatabaseError', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError', 'NotSupportedError', 'OperationalError', 'ProgrammingError', 'Warning', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__enter__', '__eq__', '__exit__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_check_executed', '_clear_result', '_conv_row', '_do_execute_many', '_do_get_result', '_ensure_bytes', '_escape_args', '_executed', '_get_db', '_nextset', '_query', '_result', '_rows', 'arraysize', 'callproc', 'close', 'connection', 'description', 'execute', 'executemany', 'fetchall', 'fetchmany', 'fetchone', 'max_stmt_length', 'mogrify', 'nextset', 'rowcount', 'rownumber', 'scroll', 'setinputsizes', 'setoutputsizes']
>>>

简单试一下：

import pymysql

sql = """
create table books(
id int auto_increment primary key,
name char(10) not null unique,
price float not null
)engine=InnoDB default charset=utf8;
"""

with pymysql.connect(host='192.168.0.105', user='jam', password='abaaba', database='somedb') as conn:
    cursor = conn.cursor()
    cursor.execute(sql)
    cursor.close()

效果如下：

mysql> use somedb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_somedb |
+------------------+
| books            |
+------------------+
2 rows in set (0.10 sec)

mysql> select * from books;    
Empty set (0.13 sec)

mysql>

深入进行：

import pymysql


conn = pymysql.connect(host='192.168.0.105', 
                           user='jam', 
                           password='abaaba', 
                           database='somedb')


def insert(cursor, val:list):
    cursor.execute('insert into books values (%d, "%s", %f)'.format(val[0], val[1], val[2]))
    # 提交完毕后的保存操作
    conn.commit()

def display_all(cursor):
    cursor.execute('select * from books')
    # 查看
    res = cursor.fetchall()
    print(res)

def update_name(cursor, id, name):
    cursor.execute('update books set name="%s" where id=%d'%(name, id))
    conn.commit()

def main():
    with conn.cursor() as cursor:
        insert(cursor, [3, "Java核心技术 卷I", 48])
        insert(cursor, [4, "Java核心技术 卷II", 48])
        display_all(cursor)
        update_name(cursor, 3, "linux是怎样工作的")
        display_all()
    conn.close()


if __name__ == '__main__':
    main()

上面的这些个练习还是在交互界面进行比较好，编写进脚本里再跑，出错一次就会次次出错，键冲突啊，语法格式啊，总之不能出错一次，否则后续很烦人。在交互界面里面不是全套都跑，分开一个个的步骤就会轻松很多。比如下面的：

from pymysql import connect



conn = connect(host='192.168.0.105', 
                user='jam', 
                password='abaaba', 
                database='somedb')

def table_init(cursor):
    sql = """
    create table books(
    id int auto_increment primary key,
    name char(10) not null unique,
    price float not null
    )engine=InnoDB default charset=utf8;
    """
    cursor.execute(sql)
    conn.commit()

def insert(cursor, id, name, price):
    cursor.execute('insert into books values ({}, "{}", {})'.format(id, name, price))
    # 提交完毕后的保存操作
    conn.commit()

def display_all(cursor):
    cursor.execute('select * from books')
    # 查看
    res = cursor.fetchall()
    print(res)

def update_name(cursor, id, name):
    cursor.execute('update books set name="%s" where id=%d'%(name, id))
    conn.commit()

def drop(cursor):
    cursor.execute('drop table books')

def main():
    try:
        cursor = conn.cursor()
        table_init(cursor)
        insert(cursor, 1, 'GO语言编程之旅', 80.9) 
        insert(cursor, 2, 'python爬虫实战', 88.1)
        insert(cursor, 3, "Java核心技术I", 48.0)
        insert(cursor, 4, "Java核心技术II", 48.0)
        display_all(cursor)
        update_name(cursor, 3, "计算机网络")
        #drop(cursor)
    finally:
        cursor.close()
        conn.close()


if __name__ == '__main__':
    main()

作为程序员，应该没有人一次编码一次过吧，我就踩了很多次坑，搞得常常要回头重置数据库，没办法，最后干脆点，作为练习嘛，添加一个错误处理，不管怎样都给我删掉这个数据库，等到终于过了，再把删掉数据库的操作函数注释掉。

三、sqlalchemy操作mysql

上面的sqlite3和pymysql这些个库操作起来，其实也就是多了个远程连接，实质上还是执行sql语句而已，没有过多的突出的功能了，在各种业务变得繁多而重的时候，解耦和耦合就显得很重要，而在高级语言中，针对数据库的一个特性--ORM技术，就是各论各的，高级语言可以操作自己的对象，操作完毕，就会映射到数据库中的关系数据中，也就是多个中间人转换，不用另外去学习sql的语法，前提是已经有对应的可操作数据表(一般如此)。

python的ORM框架是SQLAlchemy，安装就是pip install sqlalchemy即可，

前面一直针对书名和书的价格做数据库实验，放到python中就是下面这样的：

>>> books = [
... [1, "GO语言编程之旅", 89.0],
... [2, "python爬虫实战", 79.6],
... [3, "Java核心技术I", 44.4],
... ]
>>> type(books)
<class 'list'>
>>> type(books[0])
<class 'list'>
>>>

ORM技术就是在python中操作这类标准类型的数据，丢给ORM，然后由它转换成对应关系型数据，再进行对应的增删改查操作。接下来在前面已经建表的基础上，来进行连接，然后进行增删改查的操作。

存储数据，很多语言第一时间想到的应该都是struct，但python没有struct，只有class，另一个就是golang，它只有struct而没有class，它们的实现类型都很纯粹。

和它们的这种纯粹相比，cpp的struct就显得比较中立，既可纯数据也可面向对象。

	mysql中数值类型	python ORM数值类型
整数	INTYINT、SMALLINT、INT、INTEGER、BIGINT	SMALLINT、INT、INTEGER、BIGINT
浮点数	FLOAT、DOUBLE、DECIMAL	Float、DECIMAL
日期和时间类型	DATE、TIME、YEAR、DATETIME、TIMESTAMP	Date、Time、DateTime、TIMESTAMP
字符串类型	CHAR、VARCHAR、TINYTEXT、Text、MEDIUMTEXT、LONGTEXT	CHAR、VARCHAR、String、TEXT、UnicodeText

from sqlalchemy import Column, String, Float, Integer, create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from urllib.parse import quote_plus


# 这部分可以写进设置里，在见过的较大的项目里这类信息都是在Config文件夹里的
Host = '192.168.0.105'
User = 'jam'
Pwd = 'abaaba'
Database = 'somedb'

Base = declarative_base()

# 对应mysql books数据表
class Book(Base):
    # 表名
    __tablename__ = 'books'

    # 表成员，对应数据的实质操作对象
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    price = Column(Float(10))

# 初始化连接 以'数据库+数据库驱动://用户:密码@主机地址:端口/database'的形式
uri = 'mysql+pymysql://%s:%s@%s:3306/%s'%(User, quote_plus(Pwd), Host, Database)
print(uri)
engine = create_engine(uri)
db_session = sessionmaker(bind=engine)

# 建表，自动查找BaseModel子类，并在数据库中建立对应的表
Base.metadata.create_all(engine)
# 删表
Base.metadata.drop_all(engine)

踩了不少坑吧，首先是由于数据库uri的问题，在password@host这一部分出了问题，因为password中带有'@'字符，所以转义方面处理好，导致最后字符串解析得到的host是'@192.168.0.105'，暂时不太清楚转义的处理，所以选择了降版本，把sqlalchemy降级到1.3.19，再来连接就没有出错(不过却和我的flask的依赖冲突了，有点烦，虚拟环境还是很有必要的)。

后续的解决是用urllib.parse.quote_plus(uri)解决了，顺便把sqlalchemy的库更新了，不过这时候就有了个警告，是declarative_base迁移到了orm中而不是原来的ext.declarative中。

后续的表操作：

# 进行操作
session = db_session()
book1 = Book(id=1, name='GO语言编程之旅', price=89.6)
book2 = Book(id=2, name='python爬虫实战', price=67.1)
book3 = Book(id=3, name='linux是怎样运行的', price=89.6)
book4 = Book(id=4, name='夜航船', price=55)

# 增加多条
session.add_all([book1, book2])
# 保存提交
session.commit()
# 增加一条
session.add(book3)
session.commit()

# 查一哈顺便插一哈
sql_query = session.query(Book)
print(sql_query)
books = sql_query.all()
for book in books:
    print(book.id, book.name, book.price)
session.add(book4)
session.commit()

# 条件查询
book = session.query(Book).filter(Book.name=='python爬虫实战').one()
print()
print(book.id, book.name, book.price)
books = session.query(Book).filter(Book.price==89.6).all()
for book in books:
    print("价格都是89.6的书：\n", book.name)

# 模糊查询，加上通配符
go_books = session.query(Book).filter(Book.name.like('GO%')).all()
print("名字带有GO的书数：", len(go_books), ", 书名：",go_books[0].name)

# 更新
session.query(Book).filter(Book.id==1).update({'name':'我是李大爷'})
session.commit()

# 排个序
allbooks = session.query(Book).order_by(Book.price).all()
for book in allbooks:
    print(book.id, book.name, book.price)

session.close()

另一个就是，本来想利用上下文管理器来管理sessionmaker类对象，但失败了，因为它没有实现__enter__方法，入口都没有的。

总结一下，先整个declarative_base的基类，然后定义继承了该基类的后续操作的存储数据对象，再接下来就是连接的获取了，后续对表的操作都基于连接。