python数据库学习

python进行MySQL数据库连接

python进行数据库连接比java要容易一些，不需要进行驱动的导入，导包之后就能直接进行连接使用

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

进行数据信息的插入操作

cur = db.cursor() #声明一个游标
cur = db.cursor() #声明一个游标
cur.execute('DROP TABLE IF EXISTS Student')#创建表之前先检查是否存在，如果存在则删除
sqlQuery = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age int )"
cur.execute(sqlQuery)
sqlQuery=" INSERT INTO Student (Name, Email, Age) VALUE (%s,%s,%s) "
value=('bbb','123456@163.com',20)
try:
    cur.execute(sqlQuery,value)
    db.commit()
    print('数据插入成功！')
except pymysql.Error as e:
    print("数据插入失败："+e )
    db.rollback()

进行数据的查询操作，指定查询和全部查询

sqlQuery = "SELECT * FROM Student"
# sqlQuery = "SELECT * FROM Student where Name ='aaa'"
try:
    cur.execute(sqlQuery)
    results=cur.fetchall()
    print("查询团队成员信息：")
    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))

进行数据的更新修改操作

sqlQuery = "UPDATE Student SET Name= %s WHERE Name=%s"
value = ('John', 'updated name')
try:
    cur.execute(sqlQuery, value)
    db.commit()
    print('数据更新成功！')
except pymysql.Error as e:
    print("数据更新失败："+str(e))
    # 发生错误时回滚
    db.rollback()

进行数据的删除操作

sqlQuery = "DELETE FROM Student where Name=%s"