Python操作数据库pymysql

GitHub地址：https://github.com/PyMySQL/PyMySQL

1. 安装pymysql

pip install pymysql

2. 案例

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('[email protected]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

3. 基本操作

• （1）导入pymysql: import pymysql
• （2）连接数据库：conn=pymysql.connect(host='localhost',user='root',passwd='root',db='ere'，charset='utf8') 务必注意各等号前面的内容！charset参数可避免中文乱码。
• （3）获取操作游标： cur=conn.cursor()
• （4）执行sql语句,插入记录：sta=cur.execute("insert 语句")  执行成功后sta值为1。更新、删除语句与此类似。
• （5）执行sql语句，查询记录：cur.execute("select语句") 执行成功后cur变量中保存了查询结果记录集，然后再用循环打印结果：

for each in cur:
      print(each[1].decode('utf-8'))     # each[1] 表示当前游标所在行的的第2列值，如果是中文则需要处理编码