PyMYSQL
安装 PyMySQL
pip install pymysql
创建数据库表
import pymysql
host = "127.0.0.1"
username = "root"
password = "root"
database = "python_db"
port = 3306
conn = pymysql.connect(host, username, password, database, port)
cur = conn.cursor()
sql = """
create table t_student(
sno int(10) primary key auto_increment,
sname varchar(20) not null,
age int(3) not null,
score float(3, 1)
)
"""
try:
cur.execute(sql)
except Exception as e:
print(e.args)
print('创建表失败')
finally:
print('创建表成功')
conn.close()
添加数据
方法 | 作用 |
---|
execute | 插入单条数据 |
executemany | 插入多条数据 |
import pymysql
def getCursor():
host = "127.0.0.1"
username = "root"
password = "root"
database = "python_db"
port = 3306
conn = pymysql.connect(host, username, password, database, port)
cur = conn.cursor()
return conn, cur
if __name__ == '__main__':
conn, cur = getCursor()
sql = "insert into t_student(sname, age, score) values(%s, %s, %s)"
zhangsan = ('张三', 20, 89.1)
list1 = [
('李四', 20, 49.1),
('王五', 20, 69.2),
('赵六', 20, 79.3),
('小七', 18, 99.9)
]
try:
cur.executemany(sql, list1)
conn.commit()
except Exception as e:
print(e.args)
print('插入失败')
conn.rollback()
finally:
conn.close()
查询数据
方法 | 作用 |
---|
findone | 获取单条返回结果 |
findall | 获取所有返回结果 |
查询多条数据
import pymysql
from Student import Student
def getCursor():
host = "127.0.0.1"
username = "root"
password = "root"
database = "python_db"
port = 3306
conn = pymysql.connect(host, username, password, database, port)
cur = conn.cursor()
return conn, cur
if __name__ == '__main__':
conn, cur = getCursor()
sql = "select * from t_student"
try:
cur.execute(sql)
students = cur.fetchall()
stuList = []
for stu in students:
print(stu)
s = Student(stu[0], stu[1], stu[2], stu[3])
stuList.append(s)
print('----------------------------')
for stu in stuList:
print(stu)
except Exception as e:
print(e.args)
finally:
conn.close()
查询单条数据
import pymysql
from Student import Student
def getCursor():
host = "127.0.0.1"
username = "root"
password = "root"
database = "python_db"
port = 3306
# 连接数据库,获取连接对象
conn = pymysql.connect(host, username, password, database, port)
# 获取 cursor 游标对象,用于操作数据库
cur = conn.cursor()
# 返回 cursor 对象
return conn, cur
if __name__ == '__main__':
conn, cur = getCursor()
sql = "select * from t_student where sname = %s"
try:
# 执行 SQL 语句
cur.execute(sql, '张三')
# 获取结果集
zhangsan = cur.fetchone()
print(zhangsan)
except Exception as e:
print(e.args)
finally:
conn.close()
更新数据
import pymysql
from Student import Student
def getCursor():
host = "127.0.0.1"
username = "root"
password = "root"
database = "python_db"
port = 3306
conn = pymysql.connect(host, username, password, database, port)
cur = conn.cursor()
return conn, cur
if __name__ == '__main__':
conn, cur = getCursor()
sql = "update t_student set score = %s where sno = %s"
try:
cur.execute(sql, (99.9, 1))
conn.commit()
except Exception as e:
print(e.args)
conn.rollback()
finally:
conn.close()
删除数据
import pymysql
from Student import Student
def getCursor():
host = "127.0.0.1"
username = "root"
password = "root"
database = "python_db"
port = 3306
conn = pymysql.connect(host, username, password, database, port)
cur = conn.cursor()
return conn, cur
if __name__ == '__main__':
conn, cur = getCursor()
sql = "delete from t_student where sno = %s"
try:
cur.execute(sql, 2)
conn.commit()
except Exception as e:
print(e.args)
conn.rollback()
finally:
conn.close()