python操作MySQL数据库D23-CSDN博客

本文链接：https://blog.csdn.net/weixin_44745388/article/details/137832388

本文介绍了如何在Python中通过pymysql库连接到MySQL数据库，使用DictCursor优化查询结果，并创建了一个MysqlHandler类进行数据库操作的封装，以提高代码可重用性。

摘要由CSDN通过智能技术生成

"""从mysql读取数据"""
import pymysql
from pymysql.cursors import DictCursor
# 建立连接
conn = pymysql.connect(
    host="8.129.91.152",
    port=3306,
    user="future",
    password="123456",
    charset="utf8",
    cursorclass=DictCursor
)
print(conn)

# 初始化游标
cursor = conn.cursor()
print(cursor)
# 执行sql语句
cursor.execute("SELECT * FROM futureloan.member LIMIT 10;")

# 得到查询数据
members = cursor.fetchall()
print(members)

# # 得到一条记录
# member = cursor.fetchone()
# print(member)

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

# 游标和我们操作文件光标
# TODO：一个游标对象最好只获取一次，获取多次容易出问题

python连接MySQL数据库及查询数据封装：

import pymysql
from pymysql.cursors import DictCursor


class MysqlHandler():
    def __init__(
            self,
            host=None,
            port=3306,
            user=None,
            password=None,
            charset="utf-8",
            cursorclass=DictCursor
    ):
        self.conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            charset=charset,
            cursorclass=cursorclass
        )
        self.cursor = self.conn.cursor()

    def query(self, sql, one=True):
        self.conn.commit()
        self.cursor.execute(sql)
        if one:
            return self.cursor.fetchone()
        return self.cursor.fetchall()

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


if __name__ == '__main__':
    db = MysqlHandler(
        host="10.10.4.230",
        port=3311,
        user="root",
        password="ADWA6@dgjftd",
        charset='utf8',
        cursorclass=DictCursor
    )
    DATA = db.query("SELECT * FROM brigade.bri_train_travel LIMIT 10;")
    print(DATA)