使用PyMySQL完全数据库的基本操作：1、连接数据库（pythontest）、2、创建数据库表：例如：用户表 user（用户编号user_id、用户名user_name、用户密码user

Crystal_qwq

已于 2024-01-23 15:13:05 修改

阅读量984

点赞数

分类专栏： Python 文章标签： python mysql

于 2022-03-28 15:45:34 首次发布

本文链接：https://blog.csdn.net/m0_64498202/article/details/123796939

版权

Python 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

import mysql.connector

mydb = mysql.connector.connect(
host="localhost", # 数据库主机地址
user="root", # 数据库用户名
passwd="xxxx", # 数据库密码
database="pythontest"#数据库名
)

mycursor = mydb.cursor()

mycursor.execute("drop table if exists user")

mycursor.execute("CREATE TABLE user (name VARCHAR(255), password VARCHAR(255),email VARCHAR(255))")

#插入语句
sql = "INSERT INTO user (name, password, email) VALUES (%s, %s, %s)"
val = [
("Crastal", "111", "@111"),
("Jimi", "222", "@222"),
("Tom", "333", "@333"),
("LiHua", "444", "@444"),]
mycursor.executemany(sql, val)

mydb.commit() # 数据表内容有更新，必须使用到该语句

#查询语句
mycursor.execute("SELECT * FROM user")

myresult = mycursor.fetchall() # fetchall() 获取所有记录

for x in myresult:
print(x)

#修改语句
sql = "UPDATE user SET name = 'LH' WHERE name = 'LiHua'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, " 条记录被修改")

#删除语句
sql = "DELETE FROM user WHERE name = 'Tom'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, " 条记录删除")

完整代码展示！！！

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",       # 数据库主机地址
  user="root",    # 数据库用户名
  passwd="xxxx",   # 数据库密码
  database="pythontest"#数据库名
)
 
mycursor = mydb.cursor()

mycursor.execute("drop table if exists user")

mycursor.execute("CREATE TABLE user (name VARCHAR(255), password VARCHAR(255),email VARCHAR(255))")

#插入语句
sql = "INSERT INTO user (name, password, email) VALUES (%s, %s, %s)"
val = [
    ("Crastal", "111", "@111"),
    ("Jimi", "222", "@222"),
    ("Tom", "333", "@333"),
    ("LiHua", "444", "@444"),]
mycursor.executemany(sql, val)
 
mydb.commit()    # 数据表内容有更新，必须使用到该语句

#查询语句
mycursor.execute("SELECT * FROM user")
 
myresult = mycursor.fetchall()     # fetchall() 获取所有记录
 
for x in myresult:
  print(x)

#修改语句
sql = "UPDATE user SET name = 'LH' WHERE name = 'LiHua'"
 
mycursor.execute(sql)
 
mydb.commit()
 
print(mycursor.rowcount, " 条记录被修改")

#删除语句
sql = "DELETE FROM user WHERE name = 'Tom'"
 
mycursor.execute(sql)
 
mydb.commit()
 
print(mycursor.rowcount, " 条记录删除")

运行结果