Python操作数据库

一.python操作mysql

编写mysql工具类

# -*- coding: utf-8 -*-
# MySql工具类
 
import pymysql
 
 
class MYSQL:
    def __init__(self, host, user, pwd, db, charset=None):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
        self.charset = charset
 
    def __GetConnect(self):
        if not self.db:
            raise(NameError, "没有设置数据库信息")
        self.conn = pymysql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset=self.charset if self.charset else "utf8")
        cur = self.conn.cursor()
        if not cur:
            raise(NameError, "连接数据库失败")
        else:
            return cur
 
    def ExecQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()
        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList
 
    def ExecNonQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

调用:

# -*- coding: utf-8 -*-
# 导入mysql工具类
import mysql
 
conn = mysql.MYSQL("host", "username", "password", "database")
# 查询数据
sql = "SLECT * FROM 表名"
res_query = conn.ExecQuery(sql)
# 修改数据
sql = "UPDATE 表名 SET 字段='%s' WHERE 1=1" % "value"
conn.ExecNonQuery(sql)
# 删除数据
sql = "DELETE FROM 表名 WHERE 1=1"
conn.ExecNonQuery(sql)

二.python操作sqlserver

编写sqlserver工具类

# -*- coding: utf-8 -*-
# SQLServer工具类
 
import pymssql
 
 
class MSSQL:
    def __init__(self, host, user, pwd, db, charset=None):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
        self.charset = charset
 
    def __GetConnect(self):
        if not self.db:
            raise(NameError, "没有设置数据库信息")
        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset=self.charset if self.charset else "utf8")
        cur = self.conn.cursor()
        if not cur:
            raise(NameError, "连接数据库失败")
        else:
            return cur
 
    def ExecQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()
        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList
 
    def ExecNonQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()
    # 获取字段名
    def ExecQueryColumns(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        columns = [i[0] for i in cur.description]
        # 查询完毕后必须关闭连接
        self.conn.close()
        return columns

调用:

# -*- coding: utf-8 -*-
# 导入sqlserver工具类
import mssql
 
conn = mssql.MSSQL("host", "username", "password", "database")
# 查询数据
sql = "SLECT * FROM 表名"
res_query = conn.ExecQuery(sql)
# 修改数据
sql = "UPDATE 表名 SET 字段='%s' WHERE 1=1" % "value"
conn.ExecNonQuery(sql)
# 删除数据
sql = "DELETE FROM 表名 WHERE 1=1"
conn.ExecNonQuery(sql)