基础语法07--mysql-connector

黄振国

#安装驱动
pip3 install mysql-connector

或
python3 -m pip install mysql-connector


#使用mysql-connector连接mysql数据的库的时候，遇到如下报错：
mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

pip install MYSQL-connector-python

黄振国

#创建数据库

====================
#!/usr/bin/python3

import mysql.connector


mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd"
)

mycursor = mydb.cursor()

#创建新库
mycursor.execute("CREATE DATABASE test_python")

黄振国

#执行show命令

====================
#!/usr/bin/python3

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd"
)

mycursor = mydb.cursor()

#执行命令，查看所有库
mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

黄振国

#创建表格

====================
#!/usr/bin/python3

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")

黄振国

#插入数据

====================
#!/usr/bin/python3

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "https://www.runoob.com")
mycursor.execute(sql, val)

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

print(mycursor.rowcount, "记录插入成功。")

黄振国

#批量插入

====================
#!/usr/bin/python3
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = [
  ('Google', 'https://www.google.com'),
  ('Github', 'https://www.github.com'),
  ('Taobao', 'https://www.taobao.com'),
  ('stackoverflow', 'https://www.stackoverflow.com/')
]

mycursor.executemany(sql, val)

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

print(mycursor.rowcount, "记录插入成功。")

黄振国

#查询数据

====================
#!/usr/bin/python3

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM sites")

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

for x in myresult:
  print(x)

黄振国

#删除记录

====================
#!/usr/bin/python3

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

sql = "DELETE FROM sites WHERE name = 'stackoverflow'"

mycursor.execute(sql)

mydb.commit()

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



====================
#!/usr/bin/python3
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

sql = "DELETE FROM sites WHERE name = %s"
na = ("stackoverflow", )

mycursor.execute(sql, na)

mydb.commit()

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

黄振国

#修改记录
====================
#!/usr/bin/python3
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"

mycursor.execute(sql)

mydb.commit()

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


====================
#!/usr/bin/python3
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

sql = "UPDATE sites SET name = %s WHERE name = %s"
val = ("Zhihu", "ZH")

mycursor.execute(sql, val)

mydb.commit()

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

黄振国

#删除表

====================
#!/usr/bin/python3
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="passwd",
  database="test_python"
)
mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS sites"  # 删除数据表 sites

mycursor.execute(sql)