1. python, 打开终端
python -V
Python 3.11.0
2. 检查 PIP
pip -V
pip 22.3 from C:\Program Files\Python311\Lib\site-packages\pip (python 3.11)
2. Python 操作数据库, pymysql
pip install pymysql
Successfully installed pymysql-1.0.3
## 创建数据库
```py
import pymysql
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "create database taobao"
# 4. 执行SQL语句
cursor.execute(sql)
```
## 删除数据库
```py
import pymysql
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "drop database taobao"
# 4. 执行SQL语句
cursor.execute(sql)
```
## 查询数据库列表
```py
import pymysql
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "show databases"
# 4. 执行SQL语句
cursor.execute(sql)
res = cursor.fetchall()
for i in range(0, len(res)):
print(res[i][0])
```
## 数据表
### 创建数据表
```py
import pymysql
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306,
db = 'gzsp'
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "create table student (student_id int , student_name varchar(255))"
# 4. 执行SQL语句
cursor.execute(sql)
```
### 查看表列表
```py
import pymysql
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306,
db = 'gzsp'
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "show tables"
# 4. 执行SQL语句
cursor.execute(sql)
res = cursor.fetchall()
for i in range(0, len(res)):
print(res[i][0])
```
## 数据增删改查
```py
import pymysql
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306,
db = 'gzsp'
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "insert into student values (1, '甜甜'),(2, '笨笨')"
# 4. 执行SQL语句
cursor.execute(sql)
# 获取受影响行数
rows = con.affected_rows()
print(rows)
```
## 数据查询
```py
import pymysql
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306,
db = 'gzsp'
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "select * from student"
# 4. 执行SQL语句
cursor.execute(sql)
# 获取受影响行数
for i in range(0, len(res)):
print(str(res[i][0]) + ':' + res[i][1])
```
```py
import pymysql
from pymysql.cursors import DictCursor
# 1. 链接数据库
con = pymysql.connect(
host="127.0.0.1",
user='root',
passwd = 'root',
port=3306,
db = 'gzsp',
cursorclass=DictCursor
)
# 2. 获取指针 游标
cursor = con.cursor()
# 3. 准备SQL语句
sql = "select * from student"
# 4. 执行SQL语句
cursor.execute(sql)
res = cursor.fetchall()
# 遍历方式1
# for i in range(0, len(res)):
# print(res[i]["student_id"])
# print(res[i]["student_name"])
# 遍历方式2
for r in res:
print(r['student_name'])
```