Python常用库 - 数据库

一、概述

Python中连接数据库的包比较多,常用连接Mysql的库有三种,先对着3种有一个基本的认识:

MySQLdb, mysqlclient and MySQL connector/Python的区别?

There are three MySQL adapters for Python that are currently maintained:

Benchmarks

According to the following benchmarks, mysqlclient is faster (sometimes > 10x faster) than the pure Python clients.

From stackoverflow.

二、 Python Database API

这个DB-API就是一套规范,要求数据库相关的包需要按此执行。所以可以看到各种数据库操作流程上大同小异。

查看官网V2.0说明

三、pymysql

文档地址:https://pymysql.readthedocs.io/en/latest/

安装方法: pip install PyMySQL

3.1 执行查询

import pymysql

db = pymysql.connect(host="localhost", user="root", password="123456", db="test", port=3306)

cur = db.cursor()
sql = "select * from test where tag_name = %s and tag_val = %s"

try:
    cur.execute(sql, ("test", "test"))
    results = cur.fetchall()
    for row in results:
        print(row)
except Exception as e:
    raise e
finally:
    cur.close()
    db.close()

上面为按照索引返回,也可以按字段来返回

cur = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from test where tag_name = %s"

cur.execute(sql, ("test",))
results = cur.fetchone()
print(results)
cur.close
# Output:
{u'created_ts': datetime.datetime(2019, 3, 24, 22, 12, 52), u'tag_name': 'test', u'tag_val': 'test1', u'id': 6}

代码方式也可以通过with语句来实现,结束后自动关闭游标:

with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
    cursor.execute(sql, ("test", "test1"))
    results = cursor.fetchall()
    for row in results:
        print(row)

3.2 执行插入

sql = "insert into test (tag_name, tag_val) values (%s, %s)"

cur.execute(sql, ("test", "test"))
db.commit()
print(cur.lastrowid)
cur.close
db.close()

批量插入

sql = "insert into test (tag_name, tag_val) values (%s, %s)"

data = [
    ("test1", "test1"),
    ("test2", "test2"),
]

# 返回影响函数
affected_rows = cur.executemany(sql, data)
db.commit()
cur.close
db.close()

3.3 更新/删除操作

sql = "update test set tag_val = %s where tag_name = %s"

# 返回影响函数
affected_rows = cur.execute(sql, ("test", 'test'))
db.commit()
cur.close
db.close()

四、MySQLdb

MySQL-python又叫MySQLdb。用法与PyMySQL一致,官网上最后一次发布还是在2014年1月,目前还不支持Python3,网上查询Python3中可用PyMySQL代理。兼容方法(未验证):

import pymysql

pymysql.install_as_MySQLdb()

同时MySQLdb还Fork出来的一个分支:mysqlclient,增加了对Python3的支持。

五、MySQL-Connector/Python

六、Sqlite3

-- EOF --
最后更新于: 2019-04-02 23:08
发表于: 2016-03-21 00:12
标签: Python Mysql SQLite