Python高级培训第六课（寒假）2022.1.12

1、连接数据库

下载安装pymysql库

pip3 install pymysql

import pymysql   #导入数据库

#连接数据库
DBHOST = 'localhost'  
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

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

2、创建一张新表

import pymysql

DBHOST = 'localhost'  
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    cur = db.cursor()   # 声明一个游标
    cur.execute('DROP TABLE IF EXISTS Student')   # 创建表之前先检查是否存在，如果存在则删除
    sqlQuery = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age int )" 
  # 编辑sql语句
    cur.execute(sqlQuery)
    print("表格创建成功！")
except pymysql.Error as e:
    print('表格创建失败：'+str(e))

三、向表中插入一条数据

import pymysql

DBHOST = 'localhost'  
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    cur = db.cursor()
    sqlQuery=" INSERT INTO Student (Name, Email, Age) VALUE (%s,%s,%s) "   # 编辑sql语句
    value=('Mike','123456@163.com',20)   # 编辑准备插入的值
    cur.execute(sqlQuery,value)   # 执行aql语句
    db.commit()
    print('数据插入成功！')
except pymysql.Error as e:
    print("数据插入失败："+str(e))
    db.rollback()

db.close()

四、查询表中数据

import pymysql

DBHOST = 'localhost'  
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    cur = db.cursor()
    sqlQuery = "SELECT * FROM Student"   # 编辑sql语句
    cur.execute(sqlQuery)   # 执行aql语句
    results=cur.fetchall()   # 使用fetchall()方法接收全部的返回结果行
    for row in results:
        name=row[0]
        email=row[1]
        age=row[2]
        print('Name:%s,Email:%s,Age:%s'%(name,email,age))
except pymysql.Error as e:
    print("数据查询失败："+str(e))

db.close()

五、更新表中的数据

import pymysql

DBHOST = 'localhost'  
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    cur = db.cursor()
    sqlQuery = "UPDATE Student SET Name= %s WHERE Name=%s"   # 编辑sql语句（WHERE后接条件）
    value = ('John', 'updated name')
    cur.execute(sqlQuery, value)   # 执行aql语句
    db.commit()
    print('数据更新成功！')
except pymysql.Error as e:
    print("数据更新失败："+str(e))
    # 发生错误时回滚
    db.rollback()

db.close()

六、删除表中数据

import pymysql

DBHOST = 'localhost'  
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    cur = db.cursor()
    sqlQuery = "DELETE FROM Student where Name=%s"   # 编辑sql语句
    value = ('John')
    cur.execute(sqlQuery, value)   # 执行aql语句
    db.commit()
    print('Date Deleted Successfully')
except pymysql.Error as e:
    print("数据更新失败："+str(e))
    # 发生错误时回滚
    db.rollback()

db.close()

七、删除一张表

import pymysql

DBHOST = 'localhost'  
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    cur = db.cursor()
    sqlQuery='DROP TABLE IF EXISTS Student'   # 编辑sql语句
    cur.execute(sql)   # 执行aql语句
    print('表格删除成功！')
except pymysql.Error as e:
    print("表格删除失败："+str(e))
    # 发生错误时回滚
    db.rollback()

db.close()