python连接mysql

最新推荐文章于 2024-08-29 17:58:04 发布

原创最新推荐文章于 2024-08-29 17:58:04 发布 · 184 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #python #数据库

python 同时被 2 个专栏收录

40 篇文章

订阅专栏

mysql

9 篇文章

订阅专栏

本文详细介绍如何使用Python和pymysql库进行MySQL数据库操作，包括连接数据库、创建表、插入数据及封装常用操作的方法。适用于从Python 2升级到Python 3的开发者。

环境

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