python操作数据库，将select的结果输出到当前目录下的csv

导入所需模块

import csv
import psycopg2

psycopg2模块下载（linux），不需要积分

定义输出到csv的函数

def to_csv(res, title, header):
    """
    将获取的数据输出到csv
    :param res:执行sql，res=cursor.fetchall()输出的结果
    :param title:输出的文件名
    :param header:csv中包含的字段名
    :return:
    """
    with open(title + '.csv', 'w', encoding='utf-8') as csvfile:
        writer = csv.writer(csvfile, delimiter='|')
        writer.writerow(header)
        # 写入数据
        for row in range(0, len(res)):
            res_row = [str(item) + "\t" if item is not None else '' + "\t" for item in res[row]]  #防止数字字符串输出到csv为科学计数法
            print(res_row)
            writer.writerow(res_row)

定义执行select的sql脚本函数

def connect_database(*args):
    """
    :param args: 数据库的连接信息
    :return: 返回连接和游标
    """
    pg_conn = psycopg2.connect(
        host=args[0],
        port=args[1],
        user=args[2],
        password=args[3],
        database=args[4])
    # 建立游标
    cursor = pg_conn.cursor()
    select_sql = "select * from table_name"
    cursor.execute(select_sql)
    #获取结果
    res = cursor.fetchall()
    #获取字段
    des = cursor.description
    hea = [item[0] for item in des]
    # print(res_2,hea_2)
    # 关闭游标，关闭连接
    cursor.close()
    pg_conn.close()
    return res, hea

整体运行代码

title = "test"
res, hea = connect_database(ip,port,user,password,database)
to_csv(res, title, hea)