Python写MySQL

连接MySQL数据库：

import mysql.connector

# 建立连接
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="123456",
  database="mydatabase"
)

# 创建游标
mycursor = mydb.cursor()

创建表：

# 创建表
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

插入数据：

# 插入数据
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

# 提交更改
mydb.commit()

print(mycursor.rowcount, "record inserted.")

查询数据：

# 查询数据
mycursor.execute("SELECT * FROM customers")

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

for x in myresult:
  print(x)

更新数据：

# 更新数据
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Highway 21'"
mycursor.execute(sql)

# 提交更改
mydb.commit()

print(mycursor.rowcount, "record(s) affected")

删除数据：

# 删除数据
sql = "DELETE FROM customers WHERE address = 'Canyon 123'"
mycursor.execute(sql)

# 提交更改
mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

关闭连接：

# 关闭连接
mycursor.close()
mydb.close()