python中使用pymysql

安装

pip install PyMySQL

查表

import pymysql
# 创建connection连接
dbconn = pymysql.connect(
    host="localhost",
    database="XXX",
    user="root",
    password="XXX+",
    port=6666,
    charset='utf8'
)
# 执行sql语句
data = pd.read_sql('SELECT col1, col2, col3 FROM TABLE WHERE XXX', dbconn)

更新表

from pymysql import *


def main():
    # 创建connection连接
    conn = connect(host='', port=3306, database='', user='',
                   password='', charset='utf8')
    # 获取cursor对象
    cs1 = conn.cursor()
    # 执行sql语句
    query = "update 表名 set 字段1 = 值1 where 字段2 = '{}'".format(值2)
    cs1.execute(query)

    # 提交之前的操作，如果之前已经执行多次的execute，那么就都进行提交
    conn.commit()

    # 关闭cursor对象
    cs1.close()
    # 关闭connection对象
    conn.close()


if __name__ == '__main__':
    main()