pymysql笔记

安装库

pip install pymysql

加粗样式

import pymysql

连接数据库

import pymysql
DBHOST = '127.0.0.1'
DBUSER = 'root'
DBPASS = ''#密码
DBNAME = 'name_list'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
except pymysql.Error as e:
    print('数据库连接失败'+str(e))

数据插入

    cur = db.cursor() #声明
    cur.execute('DROP TABLE IF EXISTS MYINFO')#创建新表，MYINFO为表明
    sqlQuery = "CREATE TABLE MYINFO(Name CHAR(20) NOT NULL ,Specialty CHAR(20),Class int,ID CHAR(20) )" #sql语法
    cur.execute(sqlQuery) #执行sql语句
    print('创建成功')
    sqlQuery = " INSERT INTO MYINFO (Name, Specialty,Class,ID) VALUE (%s,%s,%s,%s) "#插入信息位置
    values = [('李宗盛', '流行音乐',4,"542006130314"),('崔健', '摇滚音乐',2,"543007130314")]
    #插入信息
    for value in values: #插入数据
        try:
            cur.execute(sqlQuery, value)
            db.commit()
            print(f'{value[0]}数据插入成功！')
        except pymysql.Error as e:
            print("数据插入失败：" + str(e))
            db.rollback()

数据删除

sqlQuery = "DELETE FROM Student where Name=%s"
value = ('John')

try:
    cur.execute(sqlQuery, value)
    db.commit()
    print('Date Deleted Successfully')
except pymysql.Error as e:
    print("数据删除失败："+str(e))
    # 发生错误时回滚
    db.rollback()

删除表

sqlQuery='DROP TABLE IF EXISTS Student'
cur.execute(sqlQuery)
print('表删除成功!')

数据查询

    cur = db.cursor()#声明
    sqlQuery = "SELECT * FROM MYINFO" #查询表名
    try:
        cur.execute(sqlQuery)
        results = cur.fetchall()
        # sqlQuery="select * from MYINFO where NAME in('崔健');" #查询姓名为--的同学的信息
        # q=cur.execute(sqlQuery)
        # print(q)
        for row in results:   ##查询姓名为--的同学的信息
            if row[0]=='':
                name = row[0]
                Specialty = row[1]
                Class = row[2]
                id=row[3]
                break
        print('Name:%s，Specialty:%s,Class:%s,ID:%s' % (name, Specialty, Class,id))#打印信息
    except pymysql.Error as e:
        print("数据查询失败：" + str(e))