请选择 进入手机版 | 继续访问电脑版
点击联系客服
客服QQ: 客服微信:
查看: 1|回复: 0

python高级-python MySQL

[复制链接]

1

主题

1

帖子

-7

积分

限制会员

积分
-7
发表于 2021-6-9 07:03:50 | 显示全部楼层 |阅读模式
目录

准备出发MySQL数据库安装MySQL驱动程序测试MySQL  Connector连接创建1、Python生成数据库1、数据库生成2、数据库存在确认2、Python生成表1、表2生成、核对表3、主键3、Python  Insert  Into  to插入的ID获取4、PPON  防止SQL注入8、Python  MySQL  Drop  Table1、删除表2、仅在表存在时删除9、Python更新表1、更新表2、防止SQL注入10、PP

准备出发

python可以在数据库应用程序中使用。

MySQL是最受欢迎的数据库之一。

MySQL  数据库

要尝试本教程中的代码示例,必须在计算机上安装MySQL。

请从这里下载免费的MySQL数据库(https://www.mysql.com/downloads/)。

安装 MySQL  驱动程序

python需要MySQL驱动程序才能访问MySQL数据库。

本教程使用驱动程序“MySQL连接器”。

建议您使用PIP安装MySQL连接器。

PIP可能已经安装在Python环境中。

将命令行移动到PIP位置,然后输入:

下载并安装Mysql连接器:

C: \.\ appdata  \ local  \ programs  \ python  \ python  36-32 \ scripts  python-m  pip  install  MySQL

现在已下载并安装MySQL驱动程序。
h2>测试 MySQL Connector
如需测试安装是否成功,或者您是否已安装 “MySQL Connector”,请创建一张包含以下内容的 Python 页面:
demo_mysql_test.py:

import mysql.connector

如果执行上述代码没有错误,则 “MySQL Connector” 已安装并待用。

创建连接
首先创建与数据库的连接。

使用 MySQL 数据库中的用户名和密码:

demo_mysql_connection.py:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword"
)
print(mydb)

现在,您可以开始使用 SQL 语句查询数据库了。


一、Python 创建数据库
1、创建数据库
如需在 MySQL 中创建数据库,请使用 “CREATE DATABASE” 语句:

实例
创建名为 “mydatabase” 的数据库:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")

如果执行上面的代码没有错误,则您已成功创建数据库。

2、检查数据库是否存在
您可以通过使用 “SHOW DATABASES” 语句列出系统中的所有数据库,检查数据库是否存在:

实例
返回系统中的数据库列表:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

或者您可以在建立连接时尝试访问数据库:
实例
尝试连接数据库 “mydatabase”:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)

如果数据库不存在,会收到错误。


二、Python 创建表
1、创建表
如需在 MySQL 中创建表,请使用 “CREATE TABLE” 语句。

请确保在创建连接时定义数据库的名称。

实例
创建表 “customers”:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

如果执行上面的代码没有错误,那么您现在已经成功创建了一个表。

2、检查表是否存在
您可以通过使用 “SHOW TABLES” 语句列出数据库中的所有表,来检查表是否存在:

实例
返回系统中的数据库列表:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
  print(x)

3、主键
创建表时,您还应该为每条记录创建一个具有唯一键的列。

这可以通过定义 PRIMARY KEY 来完成。

我们使用语句 “INT AUTO_INCREMENT PRIMARY KEY”,它将为每条记录插入唯一的编号。从 1 开始,每个记录递增 1。

实例
创建表时创建主键:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255), address VARCHAR(255))")

如果表已存在,请使用 ALTER TABLE 关键字:

实例
在已有的表上创建主键:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")


三、Python Insert Into Table
1、插入表
如需填充 MySQL 中的表,请使用 “INSERT INTO” 语句。

实例
在表 “customers” 中插入记录:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

重要:请注意语句 mydb.commit()。需要进行更改,否则表不会有任何改变。

2、插入多行
要在表中插入多行,请使用 executemany() 方法。

executemany() 方法的第二个参数是元组列表,包含要插入的数据:

实例
用数据填充 “customers” 表:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
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、获取已插入 ID
您可以通过询问 cursor 对象来获取刚插入的行的 id。

注释:如果插入不止一行,则返回最后插入行的 id。

实例
插入一行,并返回 id:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)


四、Python Select From
1、从表中选取
如需从 MySQL 中的表中进行选择,请使用 “SELECT” 语句:

实例
从表 “customers” 中选取所有记录,并显示结果:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

注释:我们用了 fetchall() 方法,该方法从最后执行的语句中获取所有行。

2、选取列
如需只选择表中的某些列,请使用 “SELECT” 语句,后跟列名:

实例
仅选择名称和地址列:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

3、使用 fetchone() 方法
如果您只对一行感兴趣,可以使用 fetchone() 方法。

fetchone() 方法将返回结果的第一行:

实例
仅获取一行:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchone()
print(myresult)


五、Python MySQL Where
1、使用筛选器来选取
从表中选择记录时,可以使用 “WHERE” 语句对选择进行筛选:

实例
选择记录为 “Park Lane 38” 的记录,结果:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  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)

2、通配符
您也可以选择以给定字母或短语开头、包含或结束的记录。

请使用 % 表示通配符:

实例
选择地址中包含单词 “way” 的记录:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  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)

3、防止 SQL 注入
当用户提供查询值时,您应该转义这些值。

此举是为了防止 SQL 注入,这是一种常见的网络黑客技术,可以破坏或滥用您的数据库。

mysql.connector 模块拥有转义查询值的方法:

实例
使用占位符 %s 方法来转义查询值:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  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)


六、Python MySQL Order By
1、结果排序
请使用 ORDER BY 语句按升序或降序对结果进行排序。

ORDER BY 关键字默认按升序对结果进行排序。若要按降序对结果进行排序,请使用 DESC 关键字。

实例
以字符顺序对姓名进行排序,结果:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  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、降序排序
请使用 DESC 关键字按降序对结果进行排序。

实例
按反转字母顺序对姓名的结果进行排序:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  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)


七、Python MySQL Delete From
1、删除记录
您可以使用 “DELETE FROM” 语句从已有的表中删除记录:

实例
删除地址为 “Mountain 21” 的任何记录:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

重要:请注意语句 mydb.commit()。需要进行更改,否则表不会有任何改变。

请注意 DELETE 语法中的 WHERE 子句:WHERE 子句指定应删除哪些记录。如果省略 WHERE 子句,则将删除所有记录!

2、防止 SQL 注入
在 delete 语句中,转义任何查询的值也是一种好习惯。

此举是为了防止 SQL 注入,这是一种常见的网络黑客技术,可以破坏或滥用您的数据库。

mysql.connector 模块使用占位符 %s 来转义 delete 语句中的值:

实例
使用占位符 %s 方法来转义值:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")


八、Python MySQL Drop Table
1、删除表
您可以使用 “DROP TABLE” 语句来删除已有的表:

实例
删除 “customers” 表:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE customers"
mycursor.execute(sql)

2、只在表存在时删除
如果要删除的表已被删除,或者由于任何其他原因不存在,那么可以使用 IF EXISTS 关键字以避免出错。

实例
删除表 “customers” (如果存在):

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS customers"
mycursor.execute(sql)


九、Python 更新表
1、更新表
您可以使用 “UPDATE” 语句来更新表中的现有记录:

实例
把地址列中的 “Valley 345” 覆盖为 “Canyoun 123”:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

重要:请注意语句 mydb.commit()。需要进行更改,否则不会表不会有任何改变。

请注意 UPDATE 语法中的 WHERE 子句:WHERE 子句指定应更新的记录。如果省略 WHERE 子句,则所有记录都将更新!

2、防止 SQL 注入
在 update 语句中,转义任何查询的值都是个好习惯。

此举是为了防止 SQL 注入,这是一种常见的网络黑客技术,可以破坏或滥用您的数据库。

mysql.connector 模块使用占位符 %s 来转义 delete 语句中的值:

实例
使用占位符 %s 方法来转义值:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")


十、Python MySQL Limit
1、限定结果
您可以使用 “LIMIT” 语句限制从查询返回的记录数:

实例
选取 “customers” 表中的前五条记录:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

2、从另一个位置开始
如果希望从第三条记录开始返回五条记录,您可以使用 “OFFSET” 关键字:

实例
从位置 3 开始返回 5 条记录:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)


十一、Python MySQL Join
1、组合两张或更多表
您可以使用 JOIN 语句,根据它们之间的相关列组合两个或多个表中的行。

假设您有 “users” 表和 “products” 表:

users

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products

{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

可以使用 users 的 fav 字段和 products 的 id 字段来组合这两个表。
实例
组合用户和产品,查看用户最喜欢的产品名称:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

注释:您可以使用 JOIN 而不是 INNER JOIN。您都会得到相同的结果。

2、LEFT JOIN
在上例中,Hannah 和 Michael 被排除在结果之外,这是因为 INNER JOIN 只显示匹配的记录。

如果希望显示所有用户,即使他们没有喜欢的产品,请使用 LEFT JOIN 语句:

实例
选择所有用户及其喜爱的产品:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"

3、RIGHT JOIN
如果您想要返回所有产品以及喜欢它们的用户,即使没有用户喜欢这些产品,请使用 RIGHT JOIN 语句:

实例
选择所有产品以及喜欢它们的用户:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"

注释:不对任何产品感兴趣的 Hannah 和 Michael 不包括在结果中。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|无图版|手机版|小黑屋|汕头@IT精英团

Powered by Discuz! X3.4 © 2021 Comsenz Inc.

GMT+8, 2021-8-3 02:40 , Processed in 0.171600 second(s), 21 queries .

快速回复 返回顶部 返回列表