python培训第六课

 pymysql连接数据库

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'password'
DBNAME = 'student'

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

向表test中插入一条数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'password'
DBNAME = 'student'

try:
    db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur = db.cursor()  # 声明一个游标
    sql = " INSERT INTO test (StuId, StuSubject,StunName) VALUE (%s,%s,%s) "
    value = (2, '软件工程', 'wxy')
    try:
        cur.execute(sql, value)
        db.commit()  # 数据库提交
        print('数据插入成功！')
    except pymysql.Error as e:
        print("数据插入失败：" + e)
        db.rollback()

except pymysql.Error as e:
    print('数据库连接失败'+str(e))

查询表中的数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'password'
DBNAME = 'student'

try:
    db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur = db.cursor()  # 声明一个游标
    sql = "SELECT * FROM test"

    try:
        cur.execute(sql)
        results = cur.fetchall()  # 使用fetchall()方法接收全部的返回结果行
        for row in results:
            StuId = row[0]
            StuSubject = row[1]
            StunName = row[2]
            print('StuId:%s,StuSubject:%s,StunName:%s' % (StuId, StuSubject, StunName))
    except pymysql.Error as e:
        print("数据查询失败：" + str(e))

except pymysql.Error as e:
    print('数据库连接失败'+str(e))

更新表中数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'password'
DBNAME = 'student'

try:
    db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur = db.cursor()  # 声明一个游标
    sqlQuery = "UPDATE test SET StuSubject= %s WHERE StuSubject= %s "
    value = ('数学', '英语')
    try:
        cur.execute(sqlQuery, value)
        db.commit()
        print('数据更新成功！')
    except pymysql.Error as e:
        print("数据更新失败：" + str(e))
        # 发生错误时回滚
        db.rollback()
    except pymysql.Error as e:
        print("数据查询失败：" + str(e))

except pymysql.Error as e:
    print('数据库连接失败'+str(e))

删除表中数据

import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'password'
DBNAME = 'student'
try:
    db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur = db.cursor()  # 声明一个游标
    sqlQuery = "DELETE FROM test where StunName=%s"
    value = ('lisa')
    try:
        cur.execute(sqlQuery, value)
        db.commit()
        print('删除成功')
    except pymysql.Error as e:
        print("数据删除失败：" + str(e))
        # 发生错误时回滚
        db.rollback()
except pymysql.Error as e:
    print('数据库连接失败'+str(e))

创建表

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'password'
DBNAME = 'student'

try:
    db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur = db.cursor()  # 声明一个游标
    sqlQuery = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age int )"
    cur.execute(sqlQuery)
    print("表创建成功")
except pymysql.Error as e:
    print("表创建失败：" + str(e))

删除表

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'password'
DBNAME = 'student'

try:
    db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur = db.cursor()  # 声明一个游标
    sqlQuery = 'DROP TABLE IF EXISTS Student'
    cur.execute(sqlQuery)
    print("表删除成功")
except pymysql.Error as e:
    print("表删除失败：" + str(e))

参考： Python连接MySQL数据库_Python全栈之巅-CSDN博客_python 连接mysql数据库