关于pymysql的连接方式

小幸运

# 1.简述 Python 连接 MySQL 数据库的方法?
import pymysql

try:
    connect = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="root",
        db='django0115',
        charset='utf8',
    )
    cursor = connect.cursor(pymysql.cursors.DictCursor)  # 获取游标
    print("数据库连接成功！")
    sql = """
    create table student_test
    (
    pNo varchar(16) primary key, 
    pName varchar(16),
    pGender varchar(8),
    pAge int
    ) """
    # cursor.execute(sql)  # 执行创建表 sql

    insert_sql = "insert into student_test (pNo,pName,pGender,pAge) values ('2','B','男',20)"
    # cursor.execute(insert_sql)   # 执行插入数据SQL

    cursor.execute("select * from student_test")  # 查询数据
    while True:
        row = cursor.fetchone()  # 每次执行获取一条数据，如果到了记录集的最后再次执行fetchone()返回 None。
        print(row)
        if not row:
            break
        print(row["pName"], row["pGender"], row["pAge"])
    # 获取所有数据

    update_sql = "update student_test set pName=%s,pGender=%s,pAge=%s where pNo=%s"
    cursor.execute(update_sql, ("xiao\"张", "C", 20, '2'))
    print(cursor.rowcount, " row updated")

    connect.commit()  # --提交数据,此时数据才真正写入数据库 执行update、insert，del 操作后需要提交数据   
    connect.close()  # 关闭数据库
except Exception as e:
    print(e)

# 2.Python 怎么样连接 Sqlite 数据库?
import sqlite3

try:
    con = sqlite3.connect("students.db")
    print("连接成功")
    con.close()
except Exception as err:
    print(err)

官方实例：

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()