python 中 mysql-connector 操作

使用 pip 命令来安装 mysql-connector：
python -m pip install mysql-connector

开始演练：

import mysql.connector

mydb = mysql.connector.connect(
    host="192.168.10.10",  # 数据库主机地址
    user="root",  # 数据库用户名
    passwd="123456",  # 数据库密码
    database="runoob_db"
)
myexec = mydb.cursor()

#删除表
sql = "drop table if exists sites"
myexec.execute(sql)


#更新表数据
# sql = "update sites set url = 'http://www.tgbus.com' where url = 'https://www.taobao.com'"
# myexec.execute(sql)
# mydb.commit()

# 删除记录
# sql = "delete from sites where name = '178'"
# myexec.execute(sql)
# mydb.commit()
# print(myexec.rowcount,"条记录删除")


# 排序默认为升序，desc 为降序
# sql = "select * from sites order by id desc limit 3 offset 1"
# myexec.execute(sql)
# myresult = myexec.fetchall()
# for x in myresult:
#     print(x)

#where 条件使用
# sql = "select * from sites where name='178'"
# myexec.execute(sql)
# myresult = myexec.fetchall()
# for x in myresult:
#     print(x)


#查询数据
# myexec.execute("select * from sites")
# myresult = myexec.fetchall()
# for x in myresult:
#     print(x)


#批量插入数据 批量插入使用 executemany() 方法
# sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
# val = [
#     ('178', 'https://www.178.com')
# ]
# myexec.executemany(sql,val)
# mydb.commit()
# print(myexec.rowcount,"记录插入成功")
# print("1条记录已插入，ID：", myexec.lastrowid)


#单条插入数据
# sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
# val = ("RUNOOB","https://www.runoob.com")
# myexec.execute(sql,val)
# mydb.commit()
# print(myexec.rowcount,"记录插入成功")

# 创建数据库
#myexec.execute("CREATE DATABASE runoob_db")

# 查询数据库
# myexec.execute("SHOW DATABASES")
# for x in myexec:
#     print(x)

# 指定数据库增加表
#myexec.execute("CREATE TABLE sites (name VARCHAR(255),url VARCHAR(255))")
# myexec.execute("SHOW TABLES")
# for x in myexec:
#     print(x)

#增加主键
# myexec.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")