python对数据库操作常用工具方法

# coding=UTF-8
import pymysql
import pymssql

# mysql连接
def get_mysql_conn(host, port, user, password, database):
    # 获取mysql连接
    return pymysql.connect(host=host, port=port, user=user, password=password, database=database, charset="utf8")

# sql server连接
def get_sql_server_conn(host, port, user, password, database):
    # 获取sql server连接
    return pymssql.connect(host=host, port=port, user=user, password=password, database=database, charset="utf8", tds_version="7.0")

# 获取sql server数据
def get_server_data(conn, fields, where, table):
    sql = "select %s from %s where %s" % (fields, table, where)
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result

# 获取sql server数据
def get_server_data_car(conn, sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result
# 获取一条信息
def get_server_data_one(conn, sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    return result

# 获取所有字段
def get_index_dict(cursor):
    index_dict=dict()
    index=0
    for desc in cursor.description:
        index_dict[desc[0]]=index
        index=index+1
    return index_dict

# 转字典 
def get_dict_data_sql(conn,sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    data=cursor.fetchall()
    index_dict=get_index_dict(cursor)
    res=[]
    for datai in data:
        resi=dict()
        for indexi in index_dict:
            resi[indexi]=datai[index_dict[indexi]]
        res.append(resi)
    return res
# 写入mysql数据
def put_mysql_data(conn, fields, table, data):
    sql = "insert into %s (%s) values" % (table, fields)
    cursor = conn.cursor()
    count = fields.count(",")
    values = ""
    for i in range(0, count + 1):
        values += "%s,"
    values = values[0:-1]
    cursor.executemany(sql + "(" + values + ")", data)
    conn.commit()
    print (sql)
    lines = cursor.rowcount
    return lines

# 执行一条sql语句
def carried_sql(conn, sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()

# 样本项目同步样本源项目 批量更新例子
def update_sql(sql, mysql_conn, data):
    cursor = mysql_conn.cursor()
    cursor.executemany(sql, data)
    mysql_conn.commit()
    lines = cursor.rowcount
    print(lines)

# 执行方法控制器
def implement_method(sql_server_conn, mysql_conn):
    #你要做的事情

# 主要函数
if __name__ == '__main__':
    # 新建mysql连接
    mysql_conn = get_mysql_conn("******ip", 3306, "用户名", "密码", "数据库名称")
    # mysql_conn2 = get_mysql_conn("127.0.0.1", 3306, "root", "12345678", "autosample_cs")
    # 新建sql server连接
    sql_server_conn = get_sql_server_conn("ip", 端口, "用户名", "密码", "数据库名称")
    # 调用同步方法
    implement_method(sql_server_conn, mysql_conn)