【python】连接MySQL数据库

【python】连接MySQL数据库

1. 连接数据库

db = pymysql.connect(host='localhost',
                     user='root',
                     port=3304,
                     password='kyoona',
                     database='student')

2.创建表

# 使用预处理语句创建表
sql = """CREATE TABLE student6 (
         NAME  CHAR(20) NOT NULL,
         SNO INT,  
         SDEPT CHAR(20) )"""

cursor.execute(sql)

3.插入信息

# SQL 插入语句
sqlQuery=" INSERT INTO Student6 (NAME, SNO, SDEPT) VALUE (%s,%s,%s) "
value = ('苑可允', 3, '网络开发1903')
try:
   # 执行sql语句
   cursor.execute(sqlQuery,value)
   # 执行sql语句
   db.commit()
   print('数据插入成功')
except pymysql.Error as e:
   # 发生错误时回滚
   print('数据插入失败' + str(e))
   db.rollback()

4.根据姓名查询

sqlQuery = "SELECT * FROM Student6 WHERE NAME = '苑可允'"


try:
    cursor.execute(sqlQuery)
    results = cursor.fetchall()
    for row in results:
        name=row[0]
        xuehao=row[1]
        njzy=row[2]
        print('姓名:%s,学号:%s,年级专业:%s' % (name,xuehao,njzy))
except pymysql.Error as e:
    print("数据查询失败：" + str(e))

4.整体代码
使用pymysql将自己的姓名、年级专业、以及学号存入表中,并且使用自己的名字条件查询，获取所有信息，打印出来。

import pymysql

db = pymysql.connect(host='localhost',
                     user='root',
                     port=3304,
                     password='kyoona',
                     database='student')

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# 使用预处理语句创建表
sql = """CREATE TABLE student6 (
         NAME  CHAR(20) NOT NULL,
         SNO INT,  
         SDEPT CHAR(20) )"""

cursor.execute(sql)

# SQL 插入语句
sqlQuery=" INSERT INTO Student6 (NAME, SNO, SDEPT) VALUE (%s,%s,%s) "
value = ('苑可允', 3, '网络开发1903')
try:
   # 执行sql语句
   cursor.execute(sqlQuery,value)
   # 执行sql语句
   db.commit()
   print('数据插入成功')
except pymysql.Error as e:
   # 发生错误时回滚
   print('数据插入失败' + str(e))
   db.rollback()

sqlQuery = "SELECT * FROM Student6 WHERE NAME = '苑可允'"


try:
    cursor.execute(sqlQuery)
    results = cursor.fetchall()
    for row in results:
        name=row[0]
        xuehao=row[1]
        njzy=row[2]
        print('姓名:%s,学号:%s,年级专业:%s' % (name,xuehao,njzy))
except pymysql.Error as e:
    print("数据查询失败：" + str(e))



# 关闭数据库连接
db.close()