Python操作MySQL方法汇总

调用pymysql库

查询数据

fetchone()表示迭代查询，每执行一次返回一个对象

import pymysql
# 创建和mysql服务端连接对象
conn = pymysql.connect(host='localhost',port=3306,user='root',password='422518',database='sell')
# 获取游标对象
cursor = conn.cursor()
# 编写sql语句
sql = 'select * from order_detail;'
# 执行sql语句
cursor.execute(sql) 
# 查询所有结果
data = cursor.fetchall()
print(data)
# 获取字段列表
print(cursor.description) 
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

执行其他命令

其他的sql命令就直接执行就ok了，都是原生命令，记得使用commnt方法提交一下，同时字符串要加引号

import pymysql
# 创建和mysql服务端连接对象
conn = pymysql.connect(host='localhost',port=3306,user='root',password='422518',database='sell')
# 获取游标对象
cursor = conn.cursor()
# 编写sql语句
data = ('小明',21)
cursor.execute('insert into stuinfo(`name`,`age`) values(?,?);',data)
'''
# 或者是
sql = "insert into stuinfo(`name`,`age`) values('%s',%s);"%('小明',21)
# 执行sql语句
cursor.execute(sql)
'''
conn.commit()  # 将修改提交到数据库
cursor.close()  # 关闭游标
conn.close()  # 关闭连接

其他操作

conn.rollback()  # 事务回滚
cursor.executemany("INSERT INTO book VALUES(?, ?, ?)", books)  # 执行多条sql，books是一个列表，每个列表是一条数据

pandas驱动

基础操作

需要使用到sqlalchemy这个第三方库作为帮助

import pandas as pd
from sqlalchemy import create_engine

# MySQL的用户：root, 密码:147369, 端口：3306,数据库：test
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')
# 查询语句，选出employee表中的所有数据
sql = ''' select * from employee;'''
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
# 输出employee表的查询结果
print(df)

# 新建pandas中的DataFrame, 只有id,num两列
df = pd.DataFrame({'id': [1, 2, 3, 4], 'name': ['zhangsan', 'lisi', 'wangwu', 'zhuliu']})
# 将新建的DataFrame储存为MySQL中的数据表，储存index列
df.to_sql('mydf', engine,if_exists='append')

代码说明：

首先创建一个engine，通过pandas的read_sql_query可以调用engine执行sql，从而获取一个二维形式的数据

使用pandas的to_sql方法可以将数据存到mysql中，第一个参数是数据表，第二个参数是engine，第三个参数是如果这个表存在了怎么办，默认是fail，就是报错，可以选择replace和append

根据库的文档，我们看到to_sql函数支持两类mysql引擎一个是sqlalchemy，另一个是sqlliet3

实例：将csv文件或者excel文件导入mysql

# -*- coding: utf-8 -*-
# 导入必要模块
import pandas as pd
from sqlalchemy import create_engine

# 初始化数据库连接，使用pymysql模块
db_info = {'user': 'root',
           'password': '123456',
           'host': 'localhost',
           'port': 3306,
           'database': 'test'
           }

engine = create_engine('mysql+pymysql://%(user)s:%(password)s@%(host)s:%(port)d/%(database)s?charset=utf8' % db_info, encoding='utf-8')

# 读取本地CSV文件
df = pd.read_csv("C:/Users/fuqia/Desktop/example.csv", sep=',')
df.to_sql('example', engine,  if_exists='replace')这种形式也可以

补充：engine.execute(sql)可以直接执行sql语句：

pymysql工具类

import pymysql

host = 'localhost'
user = 'root'
password = '422518'
database = 'short_video'

'''
连接数据库
'''
def connect_db():
    db = ''
    try:
        db = pymysql.connect(host=host,port=3306,user=user,password=password,database=database)
    except Exception as e:
        print('连接数据库出错')
        print(e)
    return db


'''
执行MySQL查询命令，返回结果
result格式：
[{'id':1,'username':'xx','password':'xx'}]
'''
def select_data(sql):
    db = connect_db()
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    db.close()
    desp_list = [i[0] for i in cursor.description]  # 获取字段列表
    data = []
    for i in result:
        data.append(dict(zip(desp_list,i)))
    return data

'''
执行MySQL插入命令，返回结果
data格式
{id:'xx','username':'xxx',password:'xxxx'}
'''
def insert_data(data,table_name):
    db = connect_db()
    cursor = db.cursor()
    key_list = []
    value_list = []
    for item in data:
        key_list.append(item)
        value_list.append("'" + db.escape_string(str(data[item])) + "'")
    key = ','.join(key_list)
    value = ','.join(value_list)
    sql = 'insert into %s (%s) values (%s)'%(table_name,key,value)
    try:
        cursor.execute(sql)
        db.commit()
        db.close()
    except Exception as e:
        print(e)
        return -1

'''
删除内容,传入id属性即可
'''
def delete_data(t_id,table_name):
    assert type(t_id) is int
    db = connect_db()
    cursor = db.cursor()
    sql = 'delete from %s where id=%s'%(table_name,t_id)
    try:
        cursor.execute(sql)
        db.commit()
        db.close()
    except Exception as e:
        print(e)
        return -1

'''
更新属性，传入id和要修改的内容
data是字典格式
'''
def update_data(data,table_name):
    assert 'id' in data
    db = connect_db()
    cursor = db.cursor()
    value_list = []
    for i in list(data.items())[1:]:
        value_list.append(str(i[0])+'='+ "'" +db.escape_string(str(i[1])) +"'")
    value = ','.join(value_list)
    sql = 'update %s set %s where id=%s'%(table_name,value,data['id'])
    try:
        cursor.execute(sql)
        db.commit()
        db.close()
    except Exception as e:
        print(e)
        return -1


if __name__ == '__main__':
    data = {'id':2,'username':'xxx'}
    update_data(data,'user')
    sql = 'select * from user'
    result = select_data(sql)
    print(result)