Pymysql

安装pymysql

语法

1、建立连接， conn = pymysql.connect()

2、建立游标 ， cursor = conn.cursor()

3、执行sql， cursor.execute()

4、获取结果，cursor.fetch...()

5、关闭 游标 ，关闭连接

import pymysql

# 1、建立连接
from pymysql.cursors import DictCursor

conn = pymysql.connect(host='127.0.0.1', port=3306,
                       user='root', password='',
                       charset='utf8', database='test',
                       cursorclass=DictCursor)
print(conn)
# 2、建立游标
cursor = conn.cursor()

# 3、执行sql语句
cursor.execute('select * from member')

# 4、获取结果
# all_data = cursor.fetchall()
# print(all_data)
one_data = cursor.fetchone()
print(one_data)

# 5、使用完后，要把游标和 连接都要关闭掉
cursor.close()
conn.close()

传递参数，

第一种方式：format，但是不要用这种，容易造成错误的sql语句

第二种方式：args，使用%s做为占位符，

代码如下：

mobile = '13812340000'
# 传递参数的第一种方式：format，不要用这种方式，sql注入
# 传递参数的第二种方式：args, %s 是一个占位符，所得的值都放到后面的args里，注意args里类型是，元组、列表、字典
# cursor.execute('select * from member where mobile_phone={}'.format(mobile))
cursor.execute('select * from member where mobile_phone=%s and id=%s', args=[mobile])

封装db

import pymysql
import yaml
from pymysql.cursors import DictCursor

from config.setting import DevConfig


class DBhandler():

    def __init__(self, host='127.0.0.1', port=3306,
                user='root', password='', charset='utf8',
                database='test', cursorclass=DictCursor, **kwargs):
        self.conn = pymysql.connect(host=host, port=port,
                                    database=database, user=user,
                                    password=password, charset=charset,
                                    cursorclass=cursorclass)
        self.cursor = self.conn.cursor()

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

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


if __name__ == '__main__':
    config = DevConfig()
    f = open(config.yaml_config_path, encoding='utf-8')
    yaml_data = yaml.load(f, Loader=yaml.FullLoader)
    print(yaml_data)
    db = DBhandler(host=yaml_data['database']['host'],
                   port=yaml_data['database']['port'],
                   user=yaml_data['database']['user'],
                   password=yaml_data['database']['password'],
                   database=yaml_data['database']['database'],
                   charset=yaml_data['database']['charset'])
    res = db.query('select * from member', args=None)
    print(res)