Python 操作mysql

这里记录下python操作mysql的知识点,详细参见链接

模块安装

python2中使用模块MySQLdb
python3中使用模块pymysql

1
pip3 install pymysql

基本使用

基本操作过程就是

  • 创建连接
  • 创建游标
  • 发送语句
  • 获取数据
  • 事务提交
  • 关闭游标
  • 关闭连接
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    import pymysql
    # 创建连接
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='db1')

    # 创建游标
    cursor = conn.cursor()

    # 发送语句
    cursor.execute(sqlstatement)
    cursor.executemany(sqlstatement)

    # 获取数据
    cursor.fetchone()
    cursor.fetchmany(3)
    cursor.fetchall()

    # 事务提交,不然无法保存新建或者修改的数据
    conn.commit()

    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()

基本知识点

获取新创建数据自增ID

1
2
3
4
5
6
7
8
9
10
11
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()

# 获取最新自增ID
new_id = cursor.lastrowid

游标操作

1
2
3
4
在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

fetch 数据类型

关于默认获取的数据是元组类型,如果想要获取字典类型的数据,如下pymysql.cursors.DictCursor

1
2
3
4
5
6
7
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")

result = cursor.fetchone()

1
2
3
4
5
6
7
8
9
10
11
# 执行SQL,并返回受影响行数
effect_row = cursor.execute("update hosts set host = '172.16.0.1'")
# 执行SQL,并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '172.16.0.1' where nid > %s", (1,))

这里值得注意的是不能把上面语句替换成下面语句,否则会被sql注入
statement="update hosts set host = '172.16.0.1' where nid > %s" % 1
effect_row = cursor.execute(statement)

# 执行SQL,并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("172.16.0.1",1),("1.1.1.11",2)])

SQLAlchemy

SQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果

1
pip3 install SQLAlchemy

SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:

1
2
3
4
5
6
7
8
9
10
11
12
13
MySQL-Python
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html