Python连接MySQL数据库,并创建数据库、数据表、插入删除查询修改记录等
1.下载安装并导入pymysql库
用cmd命令pip3 install pymysql
下载安装
之后再导入库
import pymysql
2.连接并创建数据库,数据表
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'test'
try:
db=pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('数据库成功连接!')
cur=db.cursor()
cur.execute("DROP TABLE IF EXISTS student")
sql='CREATE TABLE student(name VARCHAR(20) NOT NULL,Email VARCHAR(20),Age int)'
cur.execute(sql)
except pymysql.Error as e:
print('表格创建失败:'+str(e))
3.在表中插入一条数据
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'test'
try:
db=pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('数据库成功连接!')
cur=db.cursor()
sql='INSERT INTO student(name,Email,Age) VALUE(%s,%s,%s)'
value=('Qiaoxin','169696@qq.com',19)
cur.execute(sql,value)
db.commit()
print('数据插入成功!')
except pymysql.Error as e:
print('数据插入失败:'+str(e))
db.rollback()
db.close()
3.查询表中数据**
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'test'
try:
db=pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
print('数据库成功连接!')
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('数据查询失败:'+str(e))
db.close()