Pymysql和数据库

Python连接MySQL数据库【趣学Python】_哔哩哔哩_bilibili

(33条消息) Python连接MySQL数据库_Python全栈之巅-CSDN博客_python 连接mysql数据库

Python3 MySQL 数据库连接 – PyMySQL 驱动 | 菜鸟教程 (runoob.com) 

#表格创建
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '*************'
DBNAME = 'dbtest'

try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur=db.cursor()
    cur.execute("DROP TABLE IF EXISTS Student")
    sql= "CREATE TABLE Student(Name CHAR(20)  ,Grade CHAR(20),Num int ,Major CHAR(20))"
    cur.execute(sql)
    print("表格创建成功！")
except pymysql.Error as e:
    print('表格创建失败'+str(e))

插入数据： 

 

 

#删除数据
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '********'
DBNAME = 'dbtest'
try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur=db.cursor()
    sql="delete from student where Name=%s "
    value=('ll')
    cur.execute(sql,value)
    db.commit()
    print("数据库删除成功！")

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

#查询数据
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '*********'
DBNAME = 'dbtest'
try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur=db.cursor()
    sql="SELECT * FROM student"
    cur.execute(sql)
    results=cur.fetchall()
    for row in results:
        name=row[0]
        g=row[1]
        n=row[2]
        m=row[3]
        print('%s %s %s %s'%(name,g,n,m))
except pymysql.Error as e:
    print('数据库连接失败'+str(e))
db.close()

 

#表格删除
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '*************'
DBNAME = 'dbtest'
try:
    db = pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
    cur=db.cursor()
    sql='drop table if exists student'
    cur.execute(sql)
    print("表格删除成功！")

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