Python3与MySQL

1.简单连接说明

import pymysql

# 打开数据库连接
# 依次是:localhost,用户名，密码，数据库名
db = pymysql.connect('localhost', 'root', '123456', 'webcms')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据,fetchall()方法获取多条数据
data = cursor.fetchone()
print("Database version : {0} ".format(data))
# 关闭数据库连接
db.close()

'''
安装pymysql模块：pip install pymysql
'''

2.连接MySQL数据库并查询表数据

import pymysql

db = pymysql.connect('localhost', 'root', '123456', 'webcms', charset='utf8')

sql = "select * from patinet"
cursor = db.cursor()

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        """
        对应数据库的字段信息
        """
        Id = row[0]
        Point = row[1]
        print(", Point=%s" % (Id, Point))

except:
    print("Error：unable to fetch date")

db.close()

3.查询表数据返回字典形式

import pymysql

# 添加 cursorclass = pymysql.cursors.DictCursor
db = pymysql.connect('localhost', 'root', '123456', 'webcms', charset='utf8', cursorclass=pymysql.cursors.DictCursor)


sql = "select Id, Point from patinet where Point > %s" % (100)
cursor = db.cursor()

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        print(dict(row))

except:
    print("Error：unable to fetch date")

db.close()