Python MySQL

🍦 Python可用于数据库应用程序，当前较为流行的开源关系型数据库管理系统MySQL由瑞典MySQL AB公司1995年开发。

1 MySQL

  • MySQL
    • Python需一个MySQL驱动程序用于访问MySQL数据库，这里使用MySQL Connector驱动程序。
    • 下载安装MySQL连接器，命令窗口输入：python -m pip install mysql-connector-python。
    • 命令窗口下进入Python交互界面，输入import mysql.connector回车，没有报错说明成功安装。
    • MySQL：创建数据库->CREATE DATABASE <database-name>、查看数据库->SHOW DATABASES。
1
2
3
4
5
6
7
8
9
10
import mysql.connector

mydb = mysql.connector.connect(    # 创建连接
    host="localhost",
    port="3366",                   # 自定义本地MySQL端口号为3366
    user="root",
    password="123456"              # MySQL默认密码
)

print(mydb)

1-1 创建数据库

1
2
3
4
5
6
7
8
9
10
11
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")

1-2 查看数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456"
)

mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")

for x in mycursor:
    print(x)

1-3 连接数据库

1
2
3
4
5
6
7
8
9
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"           # 尝试连接数据库，若不存在将报错
)

2 创建数据表

  • 创建数据表
    • MySQL：CREATE TABLE <table-name> (<variable-name> VARCHAR(255), ... , <variable-name> VARCHAR(255))。
    • 查看表使用的命令->SHOW TABLES。
    • 表存在时添加主键->ALTER TABLE <table-name> ADD COLUMN <variable-name> INT AUTO_INCREMENT PRIMARY KEY。
1
2
3
4
5
6
7
8
9
10
11
12
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()           # 创建一个名为customers的数据表
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

2-1 查看数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")

for x in mycursor:
    print(x)

2-2 创建唯一键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
# mycursor.execute("CREATE TABLE customers \
#                 (id INT AUTO_INCREMENT PRIMARY KEY, \
#                 name VARCHAR(255), address VARCHAR(255))"
#                  )

# 该表已存在的情况下，使用ALTER TABLE关键字进行ADD COLUMN
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

3 插入数据表

  • 插入数据表
    • MySQL：INSERT INTO <table-name> (<variable-name>, ..., <variable-name>) VALUES (%s, ..., %s)。
    • 数据表同时插入多行数据：executemany()方法的第二个参数是元组列表。
    • 获取表插入最后一行的ID：lastrowid通过询问游标对象获取插入的行ID。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Lucy", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()                      # 需要commit，否则不会对表进行任何更改
print(mycursor.rowcount, "record inserted.")

3-1 表插入多行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [                            # executemany()方法的第二个参数val是元组列表
    ("Peter", "Lowstreet 4"),
    ("Amy", "Apple st 652"),
    ("Hannah", "Mountain 21"),
    ("Michael", "Valley 345"),
    ("Sandy", "Ocean blvd 2"),
    ("Betty", "Green Grass 1"),
    ("Richard", "Sky st 331"),
    ("Susan", "One way 98"),
    ("Vicky", "Yellow Garden 2"),
    ("Ben", "Park Lane 38"),
    ("William", "Central st 954"),
    ("Chuck", "Main Road 989"),
    ("Viola", "Sideway 1633")
]
mycursor.executemany(sql, val)

mydb.commit()
print(mycursor.rowcount, "was inserted.")

3-2 获取插入ID

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Blue Village")
mycursor.execute(sql, val)

mydb.commit()                      # 通过询问游标对象来获取插入的行ID
print("1 record inserted, ID: ", mycursor.lastrowid)

4 查询数据表

  • 查询数据表
    • MySQL：SELECT * FROM <table-name>，只查询第一行数据可以使用fetchone()方法。
    • 查询选择列->SELECT <variable-name>, ..., <variable-name> FROM <table-name>。
    • 查询指定值->SELECT * FROM <table-name> WHERE <variable-name> = " <value> "。
    • 通配符查询->SELECT * FROM <table-name> WHERE <variable-name> LIKE "%<value>%"。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()     # 获取表所有行

for x in myresult:
    print(x)

4-1 查询选择列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchall()     # 获取name和address列数据

for x in myresult:
    print(x)

4-2 只查询一行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchone()     # 使用fetchone()方法获取第一行数据
print(myresult)

4-3 查询指定值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = 'SELECT * FROM customers WHERE address = "Park Lane 38"'
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

4-4 通配符查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = 'SELECT * FROM customers WHERE address LIKE "%way%"'
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

4-5 防SQL注入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

4-6 数据表排序

  • 数据表排序
    • 升序->SELECT * FROM <table-name> ORDER BY <variable-name>。
    • 降序->SELECT * FROM <table-name> ORDER BY <variable-name> DESC。

(1) 升序排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(2) 降序排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

4-7 查询表记录

  • 查询表记录
    • 查询数据表的前五条记录->SELECT * FROM <table-name> LIMIT 5。
    • 位置三开始返回五条记录->SELECT * FROM <table-name> LIMIT 5 OFFSET 2。

(1) 查询前五条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(2) 查指定五条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()           # 从位置3开始，返回5条记录
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

4-8 关联数据表

  • 关联数据表
    • 将两个表关联->INNER JOIN。
    • 主表为主左连接->LEFT JOIN。
    • 副表为主右连接->RIGHT JOIN。

(1) 创建两表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE users \
                (id INT AUTO_INCREMENT PRIMARY KEY, \
                name VARCHAR(255), fav VARCHAR(255))"
                 )                 # users表

mycursor.execute("CREATE TABLE product \
                (id INT AUTO_INCREMENT PRIMARY KEY, \
                name VARCHAR(255))"
                 )                 # product表

(2) users数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "INSERT INTO users (id, name, fav) VALUES (%s, %s, %s)"
val = [
    (1, "John", "154"),
    (2, "Peter", "154"),
    (3, "Amy", "155"),
    (4, "Hannah", ""),
    (5, "Michael", "")
]
mycursor.executemany(sql, val)

mydb.commit()
print(mycursor.rowcount, "was inserted.")

(3) product数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "INSERT INTO product (id, name) VALUES (%s, %s)"
val = [
    (154, "Chocolate Heaven"),
    (155, "Tasty Lemons"),
    (156, "Vanilla Dreams")
]
mycursor.executemany(sql, val)

mydb.commit()
print(mycursor.rowcount, "was inserted.")

(4) 将两个表关联

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()           # INNER JOIN只显示匹配的记录
sql = "SELECT users.name As user, product.name As favorite FROM users \
       INNER JOIN product ON users.fav = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(5) 左连接两个表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()           # LEFT JOIN显示所有用户
sql = "SELECT users.name As user, product.name As favorite FROM users \
       LEFT JOIN product ON users.fav = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:                 # users主表为主显示
    print(x)

(6) 右连接两个表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()           # RIGHT JOIN返回所有产品及收藏它们的用户
sql = "SELECT users.name As user, product.name As favorite FROM users \
       RIGHT JOIN product ON users.fav = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:                 # product副表为主显示
    print(x)

5 删除数据表

  • 删除数据表
    • MySQL：删除数据库中现有的数据表->DROP TABLE <table-name>。
    • 仅存在该表情况下进行删除，避免报错->DROP TABLE IF EXISTS <table-name>。
    • 删除表数据->DELETE FROM <table-name> WHERE <variable-name> = "<value>"。

5-1 删除现有表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()           # 创建一个名为test的数据表
mycursor.execute("CREATE TABLE test (name VARCHAR(255), address VARCHAR(255))")
mycursor.execute("SHOW TABLES")    # 查询现有表
for x in mycursor:
    print(x)

print("--------------")

sql = "DROP TABLE test"            # 删除刚创建的test表
mycursor.execute(sql)
mycursor.execute("SHOW TABLES")    # 查询现有表
for x in mycursor:
    print(x)

5-2 仅存在时删

1
2
3
4
5
6
7
8
9
10
11
12
13
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS test"
mycursor.execute(sql)

5-3 删除表数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()           # 创建一个名为test的数据表
mycursor.execute("CREATE TABLE test (name VARCHAR(255), address VARCHAR(255))")
mycursor.execute("ALTER TABLE test ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
sql_insert = "INSERT INTO test (name, address) VALUES (%s, %s)"
val = [                            # executemany()方法的第二个参数val是元组列表
    ("Peter", "Lowstreet 4"),
    ("Amy", "Apple st 652"),
    ("Hannah", "Mountain 21"),
    ("Michael", "Valley 345"),
    ("Sandy", "Ocean blvd 2"),
    ("Betty", "Green Grass 1"),
    ("Richard", "Sky st 331"),
    ("Susan", "One way 98"),
    ("Vicky", "Yellow Garden 2"),
    ("Ben", "Park Lane 38"),
    ("William", "Central st 954"),
    ("Chuck", "Main Road 989"),
    ("Viola", "Sideway 1633")
]
mycursor.executemany(sql_insert, val)
mydb.commit()                      # 需要commit，否则不会对表进行任何更改
print(mycursor.rowcount, "was inserted.")
mycursor.execute("SELECT * FROM test")
myresult = mycursor.fetchall()     # 获取表所有行
for x in myresult:
    print(x)

print("-----------------------------")

sql_delete = 'DELETE FROM test WHERE address = "Mountain 21"'
mycursor.execute(sql_delete)       # 删除表指定行数据
mydb.commit()                      # 需要commit，否则不会对表进行任何更改
print(mycursor.rowcount, "record(s) deleted.")
mycursor.execute("SELECT * FROM test")
myresult = mycursor.fetchall()     # 获取表所有行
for x in myresult:
    print(x)

5-4 防SQL注入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "DELETE FROM test WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)

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

6 修改数据表

  • 修改数据表
    • MySQL：UPDATE <table-name> SET <variable-name> = "<value1>" WHERE <variable-name> = "<value2>"。
    • 使用MySQL的UPDATE命令进行数据表数值的修改操作，其中value1为数据表修改后的值，value2为修改前的值。

6-1 表数值修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = 'UPDATE test SET address = "Canyon 123" WHERE address = "Valley 345"'
mycursor.execute(sql)

mydb.commit()                      # 需要commit，否则不会对表进行任何更改
print(mycursor.rowcount, "record(s) affected.")

6-2 防SQL注入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port="3366",
    user="root",
    password="123456",
    database="mydatabase"
)

mycursor = mydb.cursor()
sql = "UPDATE test SET address = %s WHERE address = %s"
val = ("Green Grass 1", "Apple st 652")
mycursor.execute(sql, val)

mydb.commit()
print(mycursor.rowcount, "record(s) affected.")
Python
#MySQL #执行查询 #数据库操作
Python MySQL
https://stitch-top.github.io/2021/10/07/python/python11-python-mysql/
作者
Dr.626
发布于
2021年10月7日 23:21:10
许可协议