Python高级学习第六课（数据库的连接与使用）2022-1-13

一、数据库连接

1、下载安装pymysql库

pip install pymysql

2、代码实现连接数据库

# -*- coding: UTF-8 -*-
# coding:utf-8

import pymysql  # 导入库

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '12345678'  # 数据库密码
DBNAME = 'new_schema'  # 数据库名称


# 捕获异常
try:
    db = pymysql.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print ("数据库成功连接：")
except pymysql.Error as e:
    print ("数据库连接失败："+str(e))

3、运行结果：

二、数据库的使用

1、创建新表
代码实现：

# -*- coding: UTF-8 -*-
# coding:utf-8

import pymysql  # 导入库

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '12345678'  # 数据库密码
DBNAME = 'new_schema'  # 数据库名称


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

运行结果：


2、向表中插入一条数据
代码实现：

# -*- coding: UTF-8 -*-
# coding:utf-8

import pymysql  # 导入库

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '12345678'  # 数据库密码
DBNAME = 'new_schema'  # 数据库名称


# 捕获异常
try:
    db = pymysql.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print ("数据库成功连接：")
    cur = db.cursor()  # 声明一个游标
    sql = 'INSERT INTO Student (Name, Email, Age) VALUE (%s, %s, %s)'  # sql语句
    value = ('Mike', '123@163.com', 20)  # 准备插入的数据
    cur.execute(sql, value)
    db.commit()
    print ("数据插入成功！")
except pymysql.Error as e:
    print ("数据插入失败："+str(e))
    db.rollback()

db.close()

运行结果：


3、查询表中数据
代码实现：

# -*- coding: UTF-8 -*-
# coding:utf-8

import pymysql  # 导入库

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '12345678'  # 数据库密码
DBNAME = 'new_schema'  # 数据库名称


# 捕获异常
try:
    db = pymysql.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print ("数据库成功连接：")
    cur = db.cursor()  # 声明一个游标
    sql = "SELECT * FROM Student"  # sql语句
    cur.execute(sql)
    results = cur.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()

运行结果：

4、更新表中数据
代码实现：

# -*- coding: UTF-8 -*-
# coding:utf-8

import pymysql  # 导入库

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '12345678'  # 数据库密码
DBNAME = 'new_schema'  # 数据库名称


# 捕获异常
try:
    db = pymysql.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print ("数据库成功连接：")
    cur = db.cursor()  # 声明一个游标
    sql = "update Student set Name=%s where Name=%s"  # sql语句
    value = ('John', 'Mike')  # 准备更新的数据
    cur.execute(sql, value)
    db.commit()
    print ("数据更新成功！")


except pymysql.Error as e:
    print ("数据更新失败："+str(e))
    db.rollback()

db.close()

运行结果：


5、删除表中数据
代码实现：

# -*- coding: UTF-8 -*-
# coding:utf-8

import pymysql  # 导入库

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '12345678'  # 数据库密码
DBNAME = 'new_schema'  # 数据库名称


# 捕获异常
try:
    db = pymysql.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print ("数据库成功连接：")
    cur = db.cursor()  # 声明一个游标
    sql = "delete from Student where Name=%s"  # sql语句
    value = ('John')  # 准备删除的数据
    cur.execute(sql, value)
    db.commit()
    print ("数据删除成功！")


except pymysql.Error as e:
    print ("数据删除失败："+str(e))
    db.rollback()

db.close()

运行结果：


6、删除表
代码实现：

# -*- coding: UTF-8 -*-
# coding:utf-8

import pymysql  # 导入库

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '12345678'  # 数据库密码
DBNAME = 'new_schema'  # 数据库名称


# 捕获异常
try:
    db = pymysql.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print ("数据库成功连接：")
    cur = db.cursor()  # 声明一个游标
    sql = "drop table if exists Student"  # sql语句
    cur.execute(sql)
    db.commit()
    print ("表删除成功！")


except pymysql.Error as e:
    print ("表删除失败："+str(e))
    db.rollback()

db.close()

运行结果：