PyCharm中使用python3链接mysql方法

背景

由于学习需要,使用PyCharm练习python3,下面为python3连接mysql数据库例子

例子

看代码之前首先解决环境配置需要
1.如果你这个项目已经有mysql连接包了,那就不需要安装了,如果没有那你就往下看
2.首先打开PyCharm的preferences
3.按照图中数字顺序操作即可

import pymysql

class MysqlHelper:
    def __init__(self, host, port, db, user, password, charset='utf8'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password
        self.charset = charset

    def open_connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, db=self.db, user=self.user, password=self.password, charset=self.charset)
        self.cursor = self.conn.cursor()

    def close_connect(self):
        self.cursor.close()
        self.conn.close()


    def crud(self, sql, params):
        try:
            self.open_connect()

            self.cursor.execute(sql, params)
            self.conn.commit()

            self.close_connect()
            print(self.cursor.rowcount)
            # 只有查询的时候才会输出结果
            self.result = self.cursor.fetchall()
            for row in self.result:
                print("ID=%s, name=%s, password=%s, img=%s, status=%s, remark=%s"%row)
        except Exception as e:
            print(e)


if __name__ == '__main__':
    # 链接mysql库
    sqlHelper = MysqlHelper('localhost', 3306, 'demo_db', 'root', '123456')

    # 增
    insertSql = "insert into user(name, password,status,remark) values(%s, %s, %s, %s)"
    insertParams = ['小明', 123, 0, '用户']
    sqlHelper.crud(insertSql, insertParams)

    # 查询
    querySql = "select * from user where id = %s"
    queryParams = [2]
    sqlHelper.crud(querySql, queryParams)

    # 改
    updateSql = "update user set status = %s where id = %s"
    updateParams = [1, 2]
    sqlHelper.crud(updateSql, updateParams)

    # 删
    deleteSql = "delete from user where id = %s"
    deleteParams = [2]
    sqlHelper.crud(deleteSql, deleteParams)

优化

对比以上方法，下面做了方法细分，可当参考：

import pymysql
import config


class MysqlHelper:
    def __init__(self, host, port, db, user, password, charset='utf8'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password
        self.charset = charset

    def open_connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, db=self.db, user=self.user, password=self.password,
                                    charset=self.charset)
        self.cursor = self.conn.cursor()

    def close_connect(self):
        self.cursor.close()
        self.conn.close()

    # 单条数据操作
    def insert_update_delete(self, sql, params):
        try:
            self.open_connect()

            self.cursor.execute(sql, params)
            self.conn.commit()
            print("操作影响行数：" + str(self.cursor.rowcount))
        except Exception as e:
            print(e)
            self.conn.rollback()
        finally:
            self.close_connect()

    # 批量操作数据
    def insert_update_delete_batch(self, sql, params):
        try:
            self.open_connect()

            self.cursor.executemany(sql, params)
            self.conn.commit()
            print("操作影响行数：" + str(self.cursor.rowcount))
        except Exception as e:
            print(e)
            self.conn.rollback()
        finally:
            self.close_connect()

    # 查询单条数据
    def select_one(self, sql, params):
        try:
            self.open_connect()

            self.cursor.execute(sql, params)
            self.conn.commit()
            return self.cursor.fetchall()[0]
        except Exception as e:
            print(e)
            return None
        finally:
            self.close_connect()

    # 查询多条数据
    def select_more(self, sql, params):
        try:
            self.open_connect()

            self.cursor.execute(sql, params)
            self.conn.commit()
            return self.cursor.fetchall()
        except Exception as e:
            print(e)
            return None
        finally:
            self.close_connect()

    # 标识类方法，可以直接被类调用 cls class的简写
    @classmethod
    def insert_batch(cls, insert_batch_sql, insert_batch_params):
        # 链接mysql库
        sql_helper = MysqlHelper(config.MYSQL_CONFIG.get('host'), config.MYSQL_CONFIG.get('port'),
                                 config.MYSQL_CONFIG.get('db_name'), config.MYSQL_CONFIG.get('user'),
                                 config.MYSQL_CONFIG.get('pwd'))
        try:
            sql_helper.insert_update_delete_batch(insert_batch_sql, insert_batch_params)
        except IndexError:
            print("批量插入发生异常。。。")
            exit(1)

    # 标识类方法，可以直接被类调用 cls class的简写
    @classmethod
    def query_one(cls, query_one_sql, query_params):
        # 链接mysql库
        sql_helper = MysqlHelper(config.MYSQL_CONFIG.get('host'), config.MYSQL_CONFIG.get('port'),
                                 config.MYSQL_CONFIG.get('db_name'), config.MYSQL_CONFIG.get('user'),
                                 config.MYSQL_CONFIG.get('pwd'))
        try:
            return sql_helper.select_one(query_one_sql, query_params)
        except IndexError:
            print("单条查询发生异常。。。")
            exit(1)


if __name__ == '__main__':
    # 链接mysql库
    sqlHelper = MysqlHelper(config.MYSQL_CONFIG.get('host'), config.MYSQL_CONFIG.get('port'),
                            config.MYSQL_CONFIG.get('db_name'), config.MYSQL_CONFIG.get('user'),
                            config.MYSQL_CONFIG.get('pwd'))

    # 增-单条
    insertSql = "insert into user(name, password,status,remark) values(%s, %s, %s, %s)"
    insertParams = ['小明', 123, 0, '用户']
    sqlHelper.insert_update_delete(insertSql, insertParams)

    # 增-批量
    insertSql = "insert into user(name, password,status,remark) values(%s, %s, %s, %s)"
    insertParams = [
        ['小红', 123, 0, '用户'],
        ['小光', 123, 0, '用户']
                    ]
    sqlHelper.insert_update_batch(insertSql, insertParams)

    # 查询 - 单条数据
    querySql = "select * from user where id = %s"
    queryParams = [14]
    result = sqlHelper.select_one(querySql, queryParams)
    for row in result:
        print(row)

    # 查询 - 批量数据
    querySql = "select * from user where name = %s"
    queryParams = "小明"
    result = sqlHelper.select_more(querySql, queryParams)
    for row in result:
        print("ID=%s, name=%s, password=%s, img=%s, status=%s, remark=%s" % row)

    # 更新 - 单条
    updateSql = "update user set status = %s where id = %s"
    updateParams = [2, 15]
    sqlHelper.insert_update_delete(updateSql, updateParams)

    # 更新 - 批量
    updateSql = "update user set status = %s where id = %s"
    updateParams = [
        [3, 15],
        [3, 16],
        [3, 17]
        ]
    sqlHelper.insert_update_batch(updateSql, updateParams)

    # 删除 - 单条
    deleteSql = "delete from user where id = %s"
    deleteParams = [14]
    sqlHelper.insert_update_delete(deleteSql, deleteParams)

    # 删除 - 批量
    deleteSql = "delete from user where id = %s"
    deleteParams = [
        [15],
        [16],
        [17]
        ]
    sqlHelper.insert_update_delete_batch(deleteSql, deleteParams)

mysql数据库连接配置信息放到了配置文件中，新建一个名为config.py的文件，将配置信息写入，然后在使用的文件中 import config.py 即可，如下：

# mysql数据库配置
MYSQL_CONFIG = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "pwd": "123456",
    "db_name": "demo_db",
    "charset": "utf8"
}