【python】-- pymsql 操作MySQL

pymysql

对MySQL数据库进行简单数据操作python模块主要是：MySQLdb、pymsql，MySQLdb模块主要用于python2.X，而python3.X则使用pymsql，pymysql的使用方法和MySQLdb几乎一样，习惯用MySQLdb的，只需 import MySQLdb 修改为 import pymysql 就可以了 

一、安装

pip3 install pymysql

二、基本数据操作

a、增

import pymysql

#打开数据库连接
db = pymysql.connect(host="localhost",
                     user="root",
                     password="123456",
                     db="mysql",
                     port=3306,
                     charset='utf8',
                     # 以字典形式展示所查询数据
                     cursorclass=pymysql.cursors.DictCursor)


try:
    with db.cursor() as cursor:  # 使用cursor()方法获取操作游标
        #  sql语句
        sql = "insert into user(id,username,password) values(4,'liu','1234')"
        cursor.execute(sql)    # 执行sql语句
        #提交
        db.commit()
except Exception as e:
    print(e)
    db.rollback()  # 回滚
finally:
    db.close()  # 关闭连接

 b、删

import pymysql

#打开数据库连接
db = pymysql.connect(host="localhost",
                     user="root",
                     password="123456",
                     db="mysql",
                     port=3306,
                     charset='utf8',
                     # 以字典形式展示所查询数据
                     cursorclass=pymysql.cursors.DictCursor)


try:
    with db.cursor() as cursor:  # 使用cursor()方法获取操作游标
        #   sql语句
        sql = "delete from user where id = %d"  
        cursor.execute(sql %(4))    # 执行sql语句,并向sql语句传递参数
        #提交
        db.commit()
except Exception as e:
    print(e)
    db.rollback() # 回滚
finally:
    db.close()  # 关闭连接

 c、查

import pymysql

#打开数据库连接
db = pymysql.connect(host="localhost",
                     user="root",
                     password="123456",
                     db="mysql",
                     port=3306,
                     charset='utf8',
                     # 以字典形式展示所查询数据
                     cursorclass=pymysql.cursors.DictCursor)


try:
    with db.cursor() as cursor:  # 使用cursor()方法获取操作游标
        #   查询语句
        sql = "select * from user"
        cursor.execute(sql)    # 执行sql语句
        results = cursor.fetchall()    # 获取查询的所有记录
        print(results)
except Exception as e:
    print(e)
finally:
    db.close()  # 关闭连接

d、改

import pymysql

#打开数据库连接
db = pymysql.connect(host="localhost",
                     user="root",
                     password="123456",
                     db="mysql",
                     port=3306,
                     charset='utf8',
                     # 以字典形式展示所查询数据
                     cursorclass=pymysql.cursors.DictCursor)


try:
    with db.cursor() as cursor:  # 使用cursor()方法获取操作游标
        #   sql语句
        sql = "update user set username = '%s' where id = %d"  
        cursor.execute(sql % ("hello", 3))    # 执行sql语句, 并传递参数
        #提交
        db.commit()
except Exception as e:
    print(e)
    db.rollback()  # 回滚
finally:
    db.close()  # 关闭连接

e、调用自定义函数

import pymysql

#打开数据库连接
db = pymysql.connect(host="localhost",
                     user="root",
                     password="123456",
                     db="mysql",
                     port=3306,
                     charset='utf8',
                     # 以字典形式展示所查询数据
                     cursorclass=pymysql.cursors.DictCursor)


try:
    with db.cursor() as cursor:  # 使用cursor()方法获取操作游标
        #  调用自动应函数并传参
         cursor.callproc(function_name,args=(data_id,phone,product_id,))
        #提交
        db.commit()
except Exception as e:
    print(e)
    db.rollback()  # 回滚
finally:
    db.close()  # 关闭连接

f、 注：

查询数据

# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()

pymysql:源码