本文为参考其他大佬的博客及视频后所做的笔记,如有侵权,请告知删除。原文链接:
参考博客:https://blog.csdn.net/hzw6991/article/details/87893761
参考视频:https://www.bilibili.com/video/av44630247?from=search
目录
数据库连接基础:
(确保连接成功)
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '******' #你自己的密码
DBNAME = 'mydb' #所要连接的数据库
try:
db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('connect success!')
except pymysql.Error as e:
print("Error connect:"+str(e))
在数据库中创建表格
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '111111111'
DBNAME = 'mydb'
try:
db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('connect success!')
cur = db.cursor() #创建游标
cur.execute("drop table if exists Student")
sql = 'create table student(Name char(20) not null, email char(20) , age int)'
cur.execute(sql)
print('表格创建成功')
except pymysql.Error as e:
print("Error connect:"+str(e))
在表格中插入数据
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '111111111'
DBNAME = 'mydb'
try:
db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('connect success!')
cur = db.cursor() #创建游标
sql = 'insert into Student(Name,email, age) value (%s,%s,%s)'
value = ('Mike','123@123.com',20)
cur.execute(sql,value)
db.commit()
print('数据插入成功')
except pymysql.Error as e:
print("Error Insert:"+str(e))
db.rollback()
db.close()
查询表格数据
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '111111111'
DBNAME = 'mydb'
try:
db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('connect success!')
cur = db.cursor() #创建游标
sql = 'select * from Student'
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("Error query:"+str(e))
db.close()
更新
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '11111111'
DBNAME = 'mydb'
try:
db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('connect success!')
cur = db.cursor() #创建游标
sql = 'update Student set Name = %s where Name = %s'
value = ('John','Mike')
cur.execute(sql,value)
db.commit()
print('数据更新成功!')
except pymysql.Error as e:
print("Error update:"+str(e))
db.rollback()
db.close()
删除数据
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '1111111'
DBNAME = 'mydb'
try:
db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('connect success!')
cur = db.cursor() #创建游标
sql = 'delete from Student where Name = %s'
value = ('John')
cur.execute(sql,value)
db.commit()
print('数据删除成功!')
except pymysql.Error as e:
print("Error delete:"+str(e))
db.rollback()
db.close()
删除表格(DROP Table)
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '11111111'
DBNAME = 'mydb'
try:
db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('connect success!')
cur = db.cursor() #创建游标
sql = 'drop table if EXISTS Student'
cur.execute(sql)
print('表格删除成功!')
except pymysql.Error as e:
print("Error drop:"+str(e))
db.close()