Python连接mysql数据库

一、 连接数据库

1、下载安装pymyql库

pip install pymysql

2、导入库

import pymysql

3、连接数据库，最好用try except捕获异常

try:
    db = pymysql.connect(host="localhost", user="root", password="****", database="test")
    print('数据库连接成功!')
except pymysql.Error as e:
    print('数据库连接失败'+str(e))

二、创建一张新表

1、声明一个游标

    cur = db.cursor()

2、创建表之前先检查是否存在，如果存在则删除

    cur.execute('DROP TABLE IF EXISTS Student')

3、编辑sql语句

   sqlQuery = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age int )"
   cur.execute(sqlQuery)

三、向表中插入一条数据

1、编辑sql语句

    sqlQuery=" INSERT INTO Student (Name, Email, Age) VALUE (%s,%s,%s) "

2、编辑准备插入的值

    value=('Mike','123456@163.com',20)

3、执行sql语句

        try:
            cur.execute(sqlQuery,value)
            db.commit()
            print('数据插入成功！')
        except pymysql.Error as e:
            print("数据插入失败："+e )
            db.rollback()

四、查询表中的数据

1、编辑sql语句

    sqlQuery = "SELECT * FROM Student"

2、使用fetchall()方法接收全部的返回结果行

        try:
            cur.execute(sqlQuery)
            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))

五、更新表中的数据

1、编辑sql语句

    sqlQuery = "UPDATE Student SET Name= %s WHERE Name=%s"

2、编辑更新的信息

    value = ('John', 'updated name')

3、提交修改

        try:
            cur.execute(sqlQuery, value)
            db.commit()
            print('数据更新成功！')
        except pymysql.Error as e:
            print("数据更新失败："+str(e))
            # 发生错误时回滚
            db.rollback()

六、删除表中的数据

1、编辑sql语句

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

2、编辑更新的信息

    value = ('John')

3、提交修改

        try:
            cur.execute(sqlQuery, value)
            db.commit()
            print('Date Deleted Successfully')
        except pymysql.Error as e:
            print("数据删除失败："+str(e))
            # 发生错误时回滚
            db.rollback()

七、删除一张表

1、编辑sql语句

    sqlQuery='DROP TABLE IF EXISTS Student'

2、提交修改

        cur.execute(sqlQuery)
        print('表删除成功!')

 

完整demo：

import pymysql

# https://blog.csdn.net/qq_39598271/article/details/112610382
# Python3.8问题
try:
    db = pymysql.connect(host="localhost", user="root", password="*****", database="test")
    print('数据库连接成功!')
    # 创建一张表
    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)

    # 向表中插入数据
    # cur = db.cursor()
    # sqlQuery = " INSERT INTO Student (Name, Email, Age) VALUE (%s,%s,%s) "
    # value = ('Mike', '123456@163.com', 20)
    # try:
    #     cur.execute(sqlQuery, value)
    #     db.commit()
    #     print('数据插入成功！')
    # except pymysql.Error as e:
    #     print("数据插入失败：" + e)
    #     db.rollback()

    # 查询表中数据
    # cur = db.cursor()
    # sqlQuery = "SELECT * FROM Student"
    # try:
    #     cur.execute(sqlQuery)
    #     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))

    # 更新表中数据
    # cur = db.cursor()
    # 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()

    # 删除表中数据
    # cur = db.cursor()
    # sqlQuery = "DELETE FROM Student where Name=%s"
    # value = ('John')
    # try:
    #     cur.execute(sqlQuery, value)
    #     db.commit()
    #     print('Date Deleted Successfully')
    # except pymysql.Error as e:
    #     print("数据删除失败：" + str(e))
    #     # 发生错误时回滚
    #     db.rollback()

    # 删除一张表
    cur = db.cursor()
    sqlQuery = 'DROP TABLE IF EXISTS Student'
    cur.execute(sqlQuery)
    print('表删除成功!')

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