python连接mysql

环境

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