py 操作Mysql数据库
1、linux下安装pymysql
pip install pymysql
2、数据表结构
CREATE TABLE `test_coin` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
`create_user` int(11) NOT NULL DEFAULT '0' COMMENT '创建人',
`amount` decimal(18,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '金额',
`create_time` int(10) NOT NULL DEFAULT '0' COMMENT '创建时间',
`modify_time` int(10) NOT NULL DEFAULT '0' COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
KEY `order_id` (`amount`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户金额表';
3、conn.py文件
#coding:utf-8
import pymysql
import time
#mysql数据库属性
host = '127.0.0.1'
port = '3306'
username = 'root'
password = '123456'
database = 'py_test'
#[1]测试数据库连接
def connMysql(sql):
#打开数据库链接
db = pymysql.connect(host, username, password, database)
#使用cursor()方法创建一个游标对象
cursor = db.cursor()
#使用execute()方法执行SQL查询
cursor.execute(sql)
#使用fetchone获取单条数据
#data = cursor.fetchone()
#使用fetchall查询多条数据
data = cursor.fetchall()
print(data)
db.close()
#connMysql('select * from test_coin');
#[2]插入数据库
def insertData():
db = pymysql.connect(host, username, password, database, charset = 'utf8')
cursor = db.cursor()
create_time = time.strftime('%Y-%m-%d %H:%M:%S')
update_time = time.strftime('%Y-%m-%d %H:%M:%S')
start_time = time.strftime('%Y-%m-%d %H:%M:%S')
end_time = time.strftime('%Y-%m-%d %H:%M:%S')
remark = '测试插入信息',update_time
print('开始')
#sql 插入一条语句
'''
sql = "insert into test_coin(uid,create_user,amount,create_time,modify_time)"\
"values ('%s','%s','%s','%s','%s')"\
%(166755433,123455,100.00,create_time,update_time)
'''
#sql 插入多条语句
sql = "insert into test_coin(uid,create_user,amount,create_time,modify_time)"\
"values ('%s','%s','%s','%s','%s'),('%s','%s','%s','%s','%s'),('%s','%s','%s','%s','%s')"\
%(166755433,123455,100.00,create_time,update_time,166755433,123455,100.00,create_time,update_time,166755433,123455,100.00,create_time,update_time)
try:
print("执行插入；")
tt = cursor.execute(sql)
print(tt) #返回执行成功的条数
db.commit()
except UnicodeEncodeError as e:
print(e)
db.rollback()
db.close()
#insertData()
#[3]查询操作
def selectData():
db = pymysql.connect(host, username, password, database, charset = 'utf8')
cursor = db.cursor()
sql = "select * from test_coin where id>='%d'"%(1)
try:
print("执行查询")
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
id = row[0]
uid = row[1]
create_user = row[2]
amount = row[3]
create_time = row[4]
modify_time = row[5]
print("id=%d,uid=%d,create_user=%d,amount=%s,create_time=%s,modify_time=%s") %(id, uid, create_user, amount, create_time, modify_time)
db.commit()
except UnicodeEncodeError as e :
print(e)
db.close()
#selectData();
#[4]更新操作
def updateData():
db = pymysql.connect(host, username, password, database, charset='utf8')
cursor = db.cursor();
update_time = time.strftime('%Y-%m-%d %H:%M:%S')
sql = "update test_coin set modify_time='%s',amount=999 where id >=%d" %(update_time, 3)
try:
print('执行更新:')
cursor.execute(sql)
db.commit();
except UnicodeEncodeError as e :
print(e)
db.rollback() #发生错误时回滚
db.close()
updateData()
#[5]删除操作
def deleteData():
db = pymysql.connect(host, username, password, database, charset='utf8')
cursor = db.cursor()
sql = "delete from test_coin where id = %d" %(4)
try:
print('执行删除')
cursor.execute(sql)
db.commit()
except UnicodeEncodeError as e:
print(e)
db.rollback()
db.close()
#deleteData();
4、备注
(1)前3句分析
#coding:utf-8 //解决字符编码乱码问题
import pymysql //导入操作mysql的模块
import time //导入时间time模块
(2)数据库创建步骤
1)创建数据库连接实例 db
2)创建一个游标 cursor
3)写/拼接 一个sql语句
4)使用游标的execute方法执行sql语句
1]如果是查询数据库的语句
cursor.execute(sql)
cursor.fetchone() 获取一条数据
cursor.fetchall() 获取全部数据
2]如果是操作数据库内容的语句，增删改
try: ... except 捕捉异常
try: 执行语句，commit提交
cursor.execute(sql)
db.commit()
except:数据回滚
db.rollback()
5)关闭数据库连接
db.close()
(3) \ 转义，不换行 %占位符
"insert into test_coin(uid,create_user,amount,create_time,modify_time)"\
"values ('%s','%s','%s','%s','%s')"\
%(166755433,123455,100.00,create_time,update_time)
\
