pymysql——Python连接SQL

                                          pymysql——Python连接SQL

 

目标：利用python进行数据库的增删改查。

环境：mysql服务 + Navicat Premium + pymysql

 

如果mysql服务 + Navicat Premium不清楚如何装？移步Mac——如何在Navicat Premium中从0到1新建数据表

 

1、pymysql库的安装

pip install pymysql

 

2、mysql服务开启

 

3、pymysql库的使用

比较简单，做个简单的笔记（部分功能没写完，没再优化）。详细文档，可以参考：

• Python MySQL - mysql-connector 驱动
• Python3 MySQL 数据库连接 - PyMySQL 驱动

#!/usr/bin/env python
# -*- coding:utf-8 -*-

"""
@Author   :geekzw
@Contact  :1223242863@qq.com
@File     :mysql_demo.py
@Time     :2021/3/28 12:06 AM
@Software :Pycharm
@Copyright (c) 2021,All Rights Reserved.
"""

import pymysql
from loguru import logger


class MySQL:
    # https://www.runoob.com/python3/python-mysql-connector.html

    def __init__(self,
                 db,
                 host='localhost',  # 127.0.0.1
                 port=3306,
                 user='root',
                 password='root',
                 charset='utf8',
                 ):

        # 建立连接
        self.conn = pymysql.connect(host=host,
                                    port=port,
                                    db=db,
                                    user=user,
                                    password=password,
                                    charset=charset)

        # 创建游标，操作设置为字典类型
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def insert(self, sql, val, batch_insert=False):
        with self.conn:
            with self.cursor:
                try:
                    if not batch_insert:
                        self.cursor.execute(sql, val)

                    else:
                        self.cursor.executemany(sql, val)

                    logger.info("SQL语句执行:" + sql)
                    self.conn.commit()

                except pymysql.Error as e:
                    logger.error(e)
                    self.conn.rollback()

    def select(self, sql, select_type="all"):
        with self.conn:
            with self.cursor:
                try:
                    self.cursor.execute(sql)  # execute执行1次, executemany执行多次
                    # 默认返回单条
                    if select_type == "one":
                        return self.cursor.fetchone()
                    elif select_type == "all":
                        return self.cursor.fetchall()

                except pymysql.Error as e:
                    logger.error(e)
                    self.conn.rollback()

    def delete(self):
        pass

    def update(self):
        pass

    def create(self, sql):
        with self.conn:
            with self.cursor:
                try:
                    self.cursor.execute(sql)
                    logger.info("SQL语句执行:" + sql)
                    self.cursor.execute("SHOW TABLES")
                    for i in self.cursor:
                        logger.info(i)

                except pymysql.Error as e:
                    logger.error(e)
                    self.conn.rollback()

    def __enter__(self):
        # 返回游标
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        # 提交数据库并执行
        self.conn.commit()
        # 关闭游标
        self.cursor.close()
        # 关闭数据库连接
        self.conn.close()


if __name__ == "__main__":
    mysql = MySQL(db="数据库")  # 自定义数据库相关配置

    # 1. 创建表格
    mysql.create("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")

    # 2. 插入
    mysql.insert(sql="INSERT INTO sites (name, url) VALUES (%s, %s)",
                 val=[
                     ('Google', 'https://www.google.com'),
                     ('Github', 'https://www.github.com'),
                     ('Taobao', 'https://www.taobao.com'),
                     ('stackoverflow', 'https://www.stackoverflow.com/')
                 ],
                 batch_insert=True)

    # 3. 查询
    for line in mysql.select(sql="select * from sites", select_type="all"):
        logger.info(line)

运行结果：