五、数据库操作pymysql

一、安装及数据库操作

安装 pip install pymysql

# mysql操作
import pymysql
from pymysql.cursors import DictCursor

# 建立连接
conn = pymysql.connect(
　　host="120.78.128.25",
　　port=3306,
　　user="future",
　　password="123456",
　　charset="utf8",
　　cursorclass=DictCursor  # 将读取出来的元组数据转换成字典
)
# 初始化游标
cursor = conn.cursor()
# 执行sql语句
cursor.execute("SELECT * FROM futureloan.member LIMIT 10;")
# 得到查询数据,得到是元组
members = cursor.fetchall()
print(members)
# 得到一条记录,再获取一次游标
cursor2 = conn.cursor()
cursor2.execute("SELECT * FROM futureloan.member LIMIT 10;")
member = cursor2.fetchone()
print(member)
# 关闭游标对象
cursor.close()
cursor2.close()
# 关闭连接对象
conn.close()
# 游标和我们操作文件光标
# 一个游标对象，最好只获取一次，获取多次很容易出问题

二、数据库代码封装

import pymysql
from pymysql.cursors import DictCursor

class MysqlHandler():

    def __init__(
            self,
            host=None,
            port=3306,
            user=None,
            password=None,
            charset='utf8',
            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.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="120.78.128.25",
    port=3306,
    user="future",
    password="123456",
    charset='utf8',
    cursorclass=DictCursor
    )

data = db.query("SELECT * FROM futureloan.member WHERE mobile_phone={} LIMIT 10;".format(13120208090))
print(data)