（19）python操作mysql,python与mysql交互(pymysql)

【1】PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

【2】安装PyMySQL

【2.1】方法一：命令窗口安装

　　

【2.2】pycharm 安装

　　

 安装后：就有了

　　

【3】使用pymysql操作mysql

【3.1】基本交互

#!/usr/bin/env python
# -*- coding:utf8 -*-
import pymysql
# 创建连接
mysql_conn = pymysql.connect(host='192.168.175.129',user='root',passwd='123456',charset='utf8',db='test')
# 创建游标、光标
mysql_cursor = mysql_conn.cursor()

# 执行sql语句
exec_rows = mysql_cursor.execute('select 1 as id union all select 2;')
print(exec_rows)
# 提交连接事务
mysql_conn.commit()

# 关闭游标
mysql_cursor.close()

# 关闭连接
mysql_conn.close()

【3.2】获取查询结果(默认是元组)

# -*- coding:utf8 -*-
import pymysql
conn = pymysql.connect(host='192.168.175.129',user='root',passwd='123456',db='test',charset='utf8')
cursor = conn.cursor()
result = cursor.execute('select * from test21;')   # 获取受影响的行数
print(result)
print(cursor.fetchone())   # 获取第一行数据
print(cursor.fetchall())   # 获取剩余所有行数据

cursor.close()
conn.close()

'''
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

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

【修改cursor查询结果fetch数据类型】

#!/usr/bin/env python
# -*- coding:utf-8 -*-
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()
  
conn.commit()
cursor.close()
conn.close()

【3.3】获取最新的自增ID

# -*- coding:utf8 -*-
import pymysql
mysql_conn = pymysql.connect(host='192.168.175.129',user='root',passwd='123456',db='test',charset='utf8')
mysql_cursor = mysql_conn.cursor()

# mysql_cursor.execute('''drop table if exists test1_1; ''')
# mysql_cursor.execute('''create table test1_1(id int primary key auto_increment,num int); ''')
mysql_cursor.execute('''insert into test1_1 values(%s,%s);''',(None,1))
mysql_conn.commit()
print(mysql_cursor.lastrowid)
mysql_conn.close()

【3.4】查询表结果，插入到另一个表

mssql 查询结果 插入到mysql，操作一样

import pymssql
import pymysql

mssql_db = {
    'mssql1': {
        'host': '192.168.191.81'
        , 'user': 'sa'
        , 'password': 'a123456!'
        , 'database': 'master'
    }
    , 'mssql2': {
        'host': '192.168.191.80'
        , 'user': 'sa'
        , 'password': 'a123456!'
        , 'database': 'master'
    }
}
mysql_db = {
    'mysql1': {
        'host': '192.168.175.129'
        , 'user': 'root'
        , 'password': '123456'
        , 'database': 'test'
    }
}
mysql_host = mysql_db['mysql1']['host']
mysql_user = mysql_db['mysql1']['user']
mysql_password = mysql_db['mysql1']['password']
mysql_database = mysql_db['mysql1']['database']
mysql_conn = pymysql.connect(host=mysql_host, user=mysql_user, password=mysql_password, database=mysql_database,charset='utf8')
mysql_cursor = mysql_conn.cursor()
for i in mssql_db:
    mssql_host = mssql_db[i]['host']
    mssql_user = mssql_db[i]['user']
    mssql_password = mssql_db[i]['password']
    mssql_database = mssql_db[i]['database']
    mssql_conn = pymssql.connect(host=mssql_host, user=mssql_user, password=mssql_password, database=mssql_database,charset='utf8')
    mssql_cursor = mssql_conn.cursor()
    mssql_sql = """select 'a' as info,'a1' as info1,1 as r
    union all
    select 'b','b1',2
    """
    mssql_cursor.execute(mssql_sql)
    rs = mssql_cursor.fetchall()
    for q in range(len(rs)):   #获取行数据
        values = rs[q]
        db_insert = 'insert into test_mysql values('
        row_len = len(rs[q])  #获取一行有多少列，列的个数
        for q1 in range(row_len):  #根据列的个数，拼接动态SQL，以便一次直接插入一行
            db_insert = db_insert+'%s,'
        db_insert = db_insert[:-1]+');'
        print(db_insert)
        mysql_cursor.execute(db_insert,values)   #直接插入一行,vlaues 为元组中的 元组（即一行）
        mysql_conn.commit()