一. 创建表以及添加数据
import pymysql#注意如果没有pymysql的包要去安装一下
#创建连接
conn = pymysql.connect(host='192.168.xxx.xxx',user='root',passwd='123456',db='test',port=3306,charset='utf8')
# 获取游标
cur = conn.cursor()
# 创建表
cur.execute("drop table if EXISTS pythonTest")
sql="""create table pythonTest (
id VARCHAR(20) NOT NULL,
name VARCHAR(20),
sex CHAR(2)
)"""
cur.execute(sql)
#conn.commit(),这里不必commit()
#添加数据
sql="insert into pythonTest(id,name,sex)values('22','tyj','男')"
try:
#执行sql语句
cur.execute(sql)
# #提交到数据库执行,这里必须commit()!!!!!!
conn.commit()
except:
#发生错误时回滚
conn.rollback()
二. 查询
import pymysql
#建立连接
conn = pymysql.connect(host="192.168.xxx.xxx",user="root",password="123456",db='test',port=3306)
#获取游标
cur = conn.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
# 执行SQL语句
cur.execute(sql)
# 获取所有记录列表
results = cur.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")
# 关闭数据库连接
conn.close()
三. 修改
import pymysql
#建立连接
conn = pymysql.connect(host="192.168.xxx.xxx",user="root",password="123456",db='test',port=3306)
#获取游标
cur = conn.cursor()
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
# 执行SQL语句
cur.execute(sql)
# 提交到数据库执行
conn.commit()
except:
# 发生错误时回滚
conn.rollback()
# 关闭数据库连接
conn.close()
四. 删除
import pymysql
#建立连接
conn = pymysql.connect(host="192.168.xxx.xxx",user="root",password="123456",db='test',port=3306)
#获取游标
cur = conn.cursor()
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
# 执行SQL语句
cur.execute(sql)
# 提交修改
conn.commit()
except:
# 发生错误时回滚
conn.rollback()
# 关闭连接
conn.close()