Python MySQL基础操作

使用工具

  • MySQL 8.x
  • mysql-connector-python

安装:

pip install mysql-connector-python

连接

连接方式:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost or others", user="your_user_name", password="your_password"
)
print(mydb)

结果:

<mysql.connector.connection_cext.CMySQLConnection object at 0x7f04e4d56460>

无报错说明安装成功。

创建Database

连接方式:

import mysql.connector

mydb = mysql.connector.connect(
  host='localhost',
  user='milk',
  password='mypassword'
  )

cursor = mydb.cursor()
cursor.execute('Create Database test')

cursor.execute('show databases')
cursor.fetchall()

个人做了简化的输出值:

[('test',)]

MySQL命令行中使用show databases;，新增据库test

Table操作

创建Table

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="milk",
  password="mypassword",
  database="test"
  )

cursor = mydb.cursor()

# 创建表 方式一
cursor.execute("Create Table student(name Varchar(255), score Int)")
cursor.execute("Alter Table student Add Column id Int Auto_Increment Primary Key")

# 创建表 方式二
cursor.execute("Create Table student (id Int Auto_Increment Primary Key, name Varchar(255), score Int)")

# 检查表
cursor.execute("show tables")

# 获取内容
cursor.fetchall()

输出值:

[('student',)]

删除任何存在的Table

sql = "Drop Table student"
cursor.execute(sql)

Table已经删除或其他原因无法退出，使用IF EXISTS

sql = "Drop Table If Exists student"
cursor.execute(sql)

Table Join操作

  • 内连接，可以使用Join替代Inner Join
  • 左连接，left join
  • 右连接，right join
sql = "select \
  a.name as a, b.name as b \
  from a inner join b \
  on a.id = b.id"

增删改查

使用mydb.commit()进行梗概。否则不做任何更改。

增 Insert Into

增加单行数据

sql = "Insert Into student(name, score) Values (%s, %s)"
val = ('soy', 60)
cursor.execute(sql, val)
mydb.commit()

增加多行数据

sql = "Insert Into student(name, score) Values(%s, %s)"
val = [
  ('xiao ming', '66'),
  ('zhang san', '77'),
  ('li si', '80')
]
cursor.executemany(sql, val)
mydb.commit()

获取插入id，插入行id，多行插入则为最后一个插入行

cursor.lastrowid

删 Delete From

使用cursor.commit()后产生变化

sql = "delete from student where name='xiao ming'"
cursor.execute(sql)
mydb.commit()

使用占位符%s预防SQL注入

sql = "delete from student where score=%s"
val = (90, )
cursor.execute(sql, val)
mydb.commit()

改 Update

使用mydb.commit()执行更改。

sql = "update student Set name = 'xiao hei' Where score=90"
cursor.execute(sql)
mydb.commit()

使用占位符%s防止SQL注入

sql = "update student set name=%s where score=%s"
val = ('xiao ming', 90)
cursor.execute(sql, val)
mydb.commit()

查 Select

cursor.execute('select * from student')
result = cursor.fetchall()  # 获取所有行
for x in result:
  print(x)

result = cursor.fetchone()  # 获取第一行

where 条件

sql = "Select * From student Where score > 80"
cursor.execute(sql)

通配符条件

使用%表示字符之间。

例如名字中包含l的同学:

sql = "Select * from student Where name Like '%l%'"
cursor.execute(sql)

数据排序 Order By

sql = "select * from student Order By score" # 默认升序
sql = "select * from student Order By score DESC" # 降序

限制数据量 LIMIT

sql = "select * from student Limit 5"

从指定位置开始 OFFSET

sql = "select * from student offset 2"

SQL注入预防

使用占位符%s，字符串或数字或其他类型

%s是固定的，可以是字符串、数字。

sql = "Select * from student where score=%d"
val = (90, )
cursor.execute(sql, val)

另一种数传递方法

sql = "select * from student where score=%(score)s"
val = {'score':90}
cursor.execute(sql, val)
comment: