MySQL ​

# 安装 pymsql 模块
pip3 install pymysql

from pymysql import connect

# 1.创建数据库连接
conn = connect(host="127.0.0.1", port=3306, user="root", password="123123", database="python", charset='utf8')
# 2.获取游标
cur = conn.cursor()

# 3.1 查询
cur.execute("SELECT * FROM students")  # 执行查询语句

# 一次性读取所有数据
# for row in cur.fetchall():
#     print(row)

# 逐条读取
while True:
    row = cur.fetchone()
    if not row:
        break
    print(row)

# 3.2 非查询

try:
    sql_insert = "INSERT INTO students (`name`,gender,classId) VALUES (%s,%s,%s)"
    params = ["Tom", 1, 2]
    cur.execute(sql_insert, params)  # 参数化查询

    # sql_update = "UPDATE students SET gender=0 WHERE id=%s"
    # cur.execute(sql_update, [6])
    # sql_delete = "delete from students where id=%s"
    # cur.execute(sql_delete, [6])

    conn.commit()
except Exception as ex:
    conn.rollback()
    print(ex)

# 4.释放资源
cur.close()
conn.close()

from pymysql import connect

with connect(host="127.0.0.1", port=3306, user="root", password="123123", database="python",
             charset='utf8') as cur:  # connect上下文管理器返回的是其游标对象而非连接对象
    try:
        sql_insert = "INSERT INTO students (`name`,gender,classId) VALUES (%s,%s,%s)"
        cur.execute(sql_insert, ["Tom", 1, 2])
        cur.connection.commit()
    except Exception as ex:
        cur.connection.rollback()
        print(ex)

from pymysql import connect
import logging


class MySqlHelper(object):
    def __init__(self, **kwargs):
        self.__connection = connect(**kwargs)
        self.__cursor = self.__connection.cursor()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.__connection is not None:
            self.__connection.close()
        if self.__cursor is not None:
            self.__cursor.close()

    def execute_datatable(self, sql, *args):
        self.__cursor.execute(sql, args)
        return self.__cursor.fetchall()

    def execute_datareader(self, sql, *args):

        self.__cursor.execute(sql, args)
        while True:
            row = self.__cursor.fetchone()
            if not row:
                break
            yield row

    def execute_nonquery(self, sql, *args, many=False):
        try:
            if many:
                affected = self.__cursor.executemany(sql, [("Tom", 1, 2), ("Jerry", 2, 1)])
            else:
                affected = self.__cursor.execute(sql, args)
            self.__cursor.connection.commit()
            return affected
        except Exception as ex:
            self.__cursor.connection.rollback()
            logging.error(ex, exc_info=1, stack_info=1)
            return 0


if __name__ == '__main__':
    db_config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '123456',
        'database': 'test'
    }
    with MySqlHelper(**db_config) as helper:
        students = helper.execute_datatable("select * from students where id<=%s", 4)
        print(students)

        students = helper.execute_datareader("select * from students where id between %s and %s", 4, 5)
        for s in students:
            print(s)

        affected = helper.execute_nonquery("delete from students where id between %s and %s", 10, 20)
        print("Affected rows: %d" % affected)

        records = (("Tom", 1, 2), ("Jerry", 2, 1))
        affected = helper.execute_nonquery("insert into students (`name`,gender,classId) values (%s,%s,%s)", *records,
                                           many=True)
        print("Affected rows: %d" % affected)


    """ 测试数据
    -- ----------------------------
    -- Table structure for students
    -- ----------------------------
    DROP TABLE IF EXISTS `students`;
    CREATE TABLE `students` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(30) NOT NULL,
      `gender` char(1) NOT NULL DEFAULT '0',
      `classId` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of students
    -- ----------------------------
    BEGIN;
    INSERT INTO `students` VALUES (1, 'Colin', '0', 1);
    INSERT INTO `students` VALUES (2, 'Robin', '0', 2);
    INSERT INTO `students` VALUES (3, 'Lily', '1', 2);
    INSERT INTO `students` VALUES (10, 'Tom', '1', 2);
    INSERT INTO `students` VALUES (11, 'Jerry', '2', 1);
    COMMIT;
    """