pymysql模块学习之基本使用

安装

pip3 install -i http://pypi.douban.com/simple pymysql --trusted-host pypi.douban.com

导入

import pymysql

创建连接

# 连接
conn = pymysql.connect(
    user='root',  # 用户名
    password='',  # 密码
    host='localhost',  # 目标主机地址
    port=3306,  # 端口, 必须为整型
    charset='utf8mb4',  # 字符集
    database='',  # 这里如果指定了数据库，后面就不用再use了
)
# print(conn)  # <pymysql.connections.Connection object at 0x00000081D8DEC400>

创建游标

cursor = conn.cursor(cursor=None)
# print(cursor)  # <pymysql.cursors.Cursor object at 0x00000081E8E6B240>

执行sql语句

sql2 = "select id, username as uname, password as pwd from userinfo where username=%s and password=%s;"
res2 = cursor.execute(query=sql2, args=(username, password))
# print(res2)  # 返回3，表示查询到的记录条数（受影响的行数）

获取结果

row = cursor.fetchone()  # 一次获取一条记录
print(row)  # (1, 'hardy9sap', '123')
# row = cursor.fetchone()  # 没有记录，返回None
# print(row)

异常信息

语法错误

pymysql.err.ProgrammingError

逻辑错误

pymysql.err.InternalError

关闭游标

cursor.close()

关闭连接

conn.close()

示例代码

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""pymysql基本使用

"""

import pymysql

# 连接
conn = pymysql.connect(
    user='root',  # 用户名
    password='',  # 密码
    host='localhost',  # 目标主机地址
    port=3306,  # 端口, 必须为整型
    charset='utf8mb4',  # 字符集
    database='',  # 这里如果指定了数据库，后面就不用再use了
)
# print(conn)  # <pymysql.connections.Connection object at 0x00000081D8DEC400>

# 创建游标

cursor = conn.cursor(cursor=None)
# print(cursor)  # <pymysql.cursors.Cursor object at 0x00000081E8E6B240>

# 不加;也可以，最好加上
sql = 'use hardy2_db;'

username = input('username, please>>> ').strip()
password = input('password, please>>> ').strip()

try:
    res = cursor.execute(query=sql)
    # print(res)  # 返回0
    sql2 = "select id, username as uname, password as pwd from userinfo where username=%s and password=%s;"
    res2 = cursor.execute(query=sql2, args=(username, password))
    # print(res2)  # 返回3，表示查询到的记录条数（受影响的行数）
    row = cursor.fetchone()  # 一次获取一条记录
    print(row)  # (1, 'hardy9sap', '123')
    # row = cursor.fetchone()  # 没有记录，返回None
    # print(row)
except pymysql.err.ProgrammingError as e:
    # 语法错误
    print(e)  # (1049, "Unknown database 'hardy2_db2'")
except pymysql.err.InternalError as e:
    # 逻辑错误
    print(e)
else:
    if row:
        print('Welcome to log in !')
    else:
        print('Failed!')
finally:
    # 关闭游标
    cursor.close()

    # 关闭连接
    conn.close()