【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:源码