环境
mysql 5.7
python 2.7.14 更新为python 3.6
pymysql 0.9.2 更新为pymysql 0.10.1
代码如下
import pymysql
# conn = pymysql.connect(server, user, password, database) # 已不再支持
conn = pymysql.connect(host='127.0.0.1', user='root', password='******', database='要连接的数据库',charset="utf8mb4") # 获取连接
cursor = conn.cursor() # 获取游标
# 删除表
cursor.execute("""DROP TABLE IF EXISTS 你的表名""")
#创建表
# ENGINE=InnoDB DEFAULT CHARSET=utf8,创建表的过程中增加这条,中文就不是乱码
creat_sql = """
CREATE TABLE 你的表名(
id int primary key auto_increment,
ip VARCHAR(45),
year VARCHAR(45),
month VARCHAR(45),
day VARCHAR(45),
time VARCHAR(45)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8"""
cursor.execute(creat_sql)
# 插入数据,注:与sqlserver有些区别
cursor.execute("INSERT INTO 你的表名 (year,month,day,time) "
"VALUES('%s','%s','%s','%s')" %(year1,month1,day1,nowtime))
# 提交数据,才会写入表格
conn.commit()
# 关闭游标关闭数据库
cursor.close()
conn.close()
封装mysql
转自 https://www.cnblogs.com/xinyangsdut/p/7687092.html
#!/usr/bin/env python2.7.14
# -*- coding: utf-8 -*-
"""
Created on 19-1-7
@author: ***
"""
import pymysql
class MysqlHelper():
def __init__(self, host, port, db, user, passwd, charset='utf8'):
self.host = host
self.port = port
self.db = db
self.user = user
self.passwd = passwd
self.charset = charset
def connect(self):
self.conn = pymysql.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)
self.cursor = self.conn.cursor()
def close(self):
self.cursor.close()
self.conn.close()
def get_one(self,sql,params=()):
result=None
try:
self.connect()
self.cursor.execute(sql, params)
result = self.cursor.fetchone()
self.close()
except Exception, e:
print e.message
return result
def get_all(self,sql,params=()):
list=()
try:
self.connect()
self.cursor.execute(sql,params)
list=self.cursor.fetchall()
self.close()
except Exception, e:
print e.message
return list
def insert(self,sql,params=()):
return self.__edit(sql,params)
def update(self, sql, params=()):
return self.__edit(sql, params)
def delete(self, sql, params=()):
return self.__edit(sql, params)
def __edit(self,sql,params):
count=0
try:
self.connect()
count=self.cursor.execute(sql,params)
self.conn.commit()
self.close()
except Exception,e:
print e.message
return count
保存为MysqlHelper.py文件。
调用类添加
# -*- coding: utf-8 -*-
from MysqlHelper import *
sql='insert intoproducts(prod_name,price) values(%s,%s)'
prod_name=raw_input("请输入产品名称:")
price=raw_input("请输入单价:")
params=[prod_name,price]
mysqlHelper=MysqlHelper('localhost',3306,'test1','root','mysql')
count=mysqlHelper.insert(sql,params)
if count==1:
print 'ok'
else:
print 'error'
调用类查询
# -*- coding: utf-8 -*-
from MysqlHelper import *
sql='select prod_name,price from products order by id '
helper=MysqlHelper('localhost',3306,'test1','root','mysql')
one=helper.get_one(sql)
print one