柚子快报邀请码778899分享：Python MySQL
01.示例: 使用 Python 创建 MySQL 数据库
# 1. 首先创建一个cursor(),然后将SQL命令作为字符串传递给execute()方法
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password"
)
cursorObject = dataBase.cursor()
#创建数据库
# cursorObject.execute("CREATE DATABASE gfg")
02.创建表格
#用于创建表的sql命令是：
# CREATE TABLE
# (
# column_name_1 column_Data_type,
# column_name_2 column_Data_type,
# :
# :
# column_name_n column_Data_type
# );
-- Active: 1665886695756@@127.0.0.1@3306@gfg
--用sql语句创建一个表:chars
CREATE TABLE `chars` (
`chr` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`chr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert into `chars`(`chr`) values ('1'),('10'),('11'),('2'),('222'),('3');
#创建表格
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
studentRecord = """CREATE TABLE STUDENT2 (
NAME VARCHAR(20) NOT NULL,
BRANCH VARCHAR(50),
ROLL INT NOT NULL,
SECTION VARCHAR(5),
AGE INT
)"""
#创建表格
cursorObject.execute(studentRecord)
#断开连接
dataBase.close()
show tables
在vscode中用的MySQL插件
03.向表格中添加数据
--用SQl语句
-- INSERT INTO table_name (column_names) VALUES (data)
INSERT INTO student2 (NAME, BRANCH, ROLL, SECTION , AGE) VALUES ("Ram", "CSE", "85", "B", "19")
用python向表格中添加数据（单行）
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
sql = "INSERT INTO STUDENT2 (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = ("jack", "CSE", "85", "B", "19")
cursorObject.execute(sql,val)
dataBase.commit()
dataBase.close()
添加多行数据
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
sql = "INSERT INTO STUDENT2 (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = [("Nikhil", "CSE", "98", "A", "18"),
("Nisha", "CSE", "99", "A", "18"),
("Rohan", "MAE", "43", "B", "20"),
("Amit", "ECE", "24", "A", "21"),
("Anil", "MAE", "45", "B", "20"),
("Megha", "ECE", "55", "A", "22"),
("Sita", "CSE", "95", "A", "19")]
cursorObject.executemany(sql,val)
dataBase.commit()
dataBase.close()
04.查询数据
--SELECT attr1, attr2 FROM table_name
SELECT AGE FROM student2
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
query = "SELECT NAME,AGE FROM STUDENT2"
cursorObject.execute(query)
result = cursorObject.fetchall()
for x in result:
print(x)
# disconnecting from server
dataBase.close()
('Ram', 19)
('jack', 19)
('Nikhil', 18)
('Nisha', 18)
('Rohan', 20)
('Amit', 21)
('Anil', 20)
('Megha', 22)
('Sita', 19)
Where Clause
--SELECT column1, column2, …. columnN FROM [TABLE NAME] WHERE [CONDITION];
SELECT NAME,AGE FROM student2 WHERE AGE>=19
# 用python
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
query = "SELECT NAME,AGE FROM STUDENT where AGE >=19"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
# disconnecting from server
dataBase.close()
('Ram', 19)
('Ram2', 19)
('Ram3', 19)
('jack', 19)
('Rohan', 20)
('Amit', 21)
('Anil', 20)
('Megha', 22)
('Sita', 19)
Order By Clause
OrderBy用于以升序或降序排列结果
DESC: 倒序
ASC:升序,ASC可以省略
--用SQL语句
/**
SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;
*/
SELECT NAME,AGE
FROM student2
ORDER BY AGE DESC;
用python排序
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
query = "SELECT NAME,AGE FROM STUDENT ORDER BY AGE DESC"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
# disconnecting from server
dataBase.close()
('Megha', 22)
('Amit', 21)
('Rohan', 20)
('Anil', 20)
('Ram', 19)
('Ram2', 19)
('Ram3', 19)
('jack', 19)
('Sita', 19)
('Nikhil', 18)
('Nisha', 18)
Limit Clause
用SQL LIMIT子句来限制SELECT语句返回的行数
SELECT
NAME,AGE
FROM
student2
ORDER BY AGE
LIMIT 3 OFFSET 0; --把结果集进行分页，每页三条记录，要获取第一页的记录
--注意，索引从0开始
SELECT
NAME,AGE
FROM
student2
ORDER BY AGE
LIMIT 3 OFFSET 3;
--要获取第2页的记录,则要跳过前面3条记录，从3号记录开始查询，OFFSET设为3
--索引从3开始
用python使用LIMIT语句
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
query = "SELECT NAME,AGE FROM STUDENT2 LIMIT 3 OFFSET 0"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
# disconnecting from server
dataBase.close()
('Ram', 19)
('jack', 19)
('Nikhil', 18)
05.更新数据
UPDATE
--基本语法：
--UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
UPDATE student2 set AGE = 20 WHERE NAME = 'jack';
--查询
SELECT * FROM student2 WHERE NAME = 'jack'
用python更新MySQl表格
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="root",
passwd ="password",
database = "gfg"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
query = "UPDATE STUDENT2 SET AGE = 23 WHERE Name ='Ram'"
cursorObject.execute(query)
dataBase.commit()
# disconnecting from server
dataBase.close()
DELETE
--sql语法：DELETE FROM TABLE_NAME WHERE ATTRIBUTE_NAME = ATTRIBUTE_VALUE
DELETE FROM student2 WHERE NAME = 'jack';
python操作的流程是一样的
Drop Tables
--DROP TABLE tablename;
--DROP TABLE IF EXISTS tablename;
DROP TABLE student
数据库中只剩下了两个表格
柚子快报邀请码778899分享：Python MySQL
发表评论