pymysql模块

安装

pip install pymysql

使用

执行单条语句

import pymysql

# 建立连接
conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='root',
                       database='blog',
                       charset='utf8')

# 创建游标
cursor = conn.cursor()

# sql语句
sql = """
	  INSERT INTO 
      users(uname, upwd, uimg)
      VALUES 
      ('zhansan', '123', 'http://...')
      """

# 执行sql语句
try:
    cursor.execute(sql)

    conn.commit()
except Exception as e:
    print('失败：', e)

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

执行多条语句

import pymysql

# 建立连接
conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='root',
                       database='blog',
                       charset='utf8')

# 创建游标
cursor = conn.cursor()

# sql语句
sql = """INSERT INTO 
      users(uname, upwd, uimg)
      VALUES 
      (%s, %s, %s)
      
      """


data = [['lisi', '123', 'http://...'], ['wangwu', '123', 'http://...'], ['zhaoliu', '123', 'http://...']]
# 执行sql语句
try:
    cursor.executemany(sql, data) # data为列表，里面可以是元组，列表，字典

    conn.commit()
except Exception as e:
    print('失败：', e)

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

封装

import pymysql


class DbOperation(object):

    def __init__(self):
        self.host = "127.0.0.1"
        self.port = 3306
        self.user = "root"
        self.password = "root"
        self.database = "users"
        self.charset = 'utf8'

        # 数据库连接
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset
        )

        # 创建游标
        self.cursor = self.conn.cursor()

    # 插入数据
    def insert_data(self, sql, data):

        try:
            self.cursor.executemany(sql, data)
            self.conn.commit()
            return 1   # 插入成功
        except Exception as e:
            self.conn.rollback()
            print('事务回滚：', e)
            return 0   # 插入失败

    # 关闭连接
    def close_conn(self):
        self.cursor.close()
        self.conn.close()