Mysql

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