PyMySQL学习笔记
一些常用函数及解释
db = pymysql.connect('host','user','password','database') # 连接数据库
cursor = db.cursor() # 创建游标对象
cursor.execute(sql) # 执行sql语句sql为sql语句
data = cursor.fetchone() # 获取单条数据(具体用法见下面示例)
data_list = fetchall() # 接收全部的返回结果行
db.commit() # 提交到数据库执行
db.rollback() # 发生错误回滚
db.close() # 关闭连接
主要操作是通过sql语句递交给execute()函数执行
以下是一些常用方法的例子
数据库创建表操作代码示例
import pymysql
# 链接数据库
db = pymysql.connect("yourhost", "yourname", "yourpassword", "yourdatabase")
# 创建游标对象
cursor = db.cursor()
# 使用execute()方法执行SQL,如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# SQL语句创建表
sql = """
CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )
"""
cursor.execute(sql)
# 关闭数据库连接
db.close()
数据库插入操作代码示例
import pymysql
config = {
"host":"yourhost",
"user":"yourname",
"password":"yourpassword",
"database":"yourdatabase"
}
db = pymysql.connect(**config)
cursor = db.cursor()
F_name = 'Dawei'
L_name = 'Wang'
age = 20
sex = 'M'
income = 2000
# 注意 %s 外加引号,作为字符,否者mysql会认为是一个column
sql = "INSERT INTO EMPLOYEE (FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) VALUES \
('%s', '%s', '%s', '%s', '%s')" %\
(F_name, L_name, age, sex, income)
try:
# 执行SQL语句
cursor.execute(sql)
# 提交到数据库执行,为了及时跟进数据库这句最好加上
db.commit()
except:
# 如果发生错误则回滚
db.rollback()
db.close()
数据库查询操作代码示例
import pymysql
config = {
"host":"yourhost",
"user":"yourname",
"password":"yourpassword",
"database":"yourdatabase"
}
db = pymysql.connect(**config)
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
# 执行语句
cursor.execute(sql)
# 获取所有列表记录
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# 打印结果
print("fname = %s, lname = %s, age = %s, sex = %s, income = %s" % \
(fname, lname, age, sex, income ))
except:
print("Error: unable to fetch data")
db.close()
数据库更新操作代码示例
import pymysql
config = {
"host":"yourhost",
"user":"yourname",
"password":"yourpassword",
"database":"yourdatabase"
}
db = pymysql.connect(**config)
cursor = db.cursor()
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
# 执行SQL语句
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
数据库删除操作代码示例
import pymysql
config = {
"host":"yourhost",
"user":"yourname",
"password":"yourpassword",
"database":"yourdatabase"
}
db = pymysql.connect(**config)
cursor = db.cursor()
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()