python-循环获得pg库中所有自建视图、函数、过程：

import psycopg2
import shutil
import os
print("输出要连接的数据库：")
database=input()
print("输入用户名：")
user=input()
print("输入密码：")
passwd=input()
print("输入数据库连接地址：")
host=input()
print("输入端口号：")
port=input()
#创建连接对象
mydb=psycopg2.connect(
    database=database,
    user=user,
    password=passwd,
    host=host,
    port=port
)
#创建指针对象
mycur=mydb.cursor()
print("选择要保存文件的地址：")
path = input()
print("选择要保存的文件名：")
filename = input()
if os.path.exists(path + './' + filename):
    shutil.rmtree(path +'./' +filename,ignore_errors=True)
else:
    pass
os.mkdir(path + './' + filename)
print("选择要到导出的结构类型(视图、函数、过程)：")
str=input()
if str=="视图":
    mycur.execute("select viewname from pg_catalog.pg_views where schemaname ='public' and viewowner ='{}'".format(user))
    view=mycur.fetchall()
    viewlen=len(view)
    j = 0
    while j < viewlen:
        a = (view[j])[0]
        mycur.execute("select ('create view '||viewname ||' as '||definition)as view_definition from pg_catalog.pg_views where schemaname ='public' and viewowner ='{}' and viewname='{}'".format(user,a))
        (myresult,) = mycur.fetchone()
        f = open(path + './' + filename + './' + a + '.sql', 'w', encoding='utf-8')
        f.write(myresult)
        f.close()
        j += 1
if str=="过程":
    mycur.execute("select a.routine_name from information_schema.routines a inner join information_schema.parameters b on a.specific_name =b.specific_name where a.specific_schema ='public' and a.routine_definition is not null and a.routine_type ='PROCEDURE' group by a.routine_name ")
    procedure=mycur.fetchall()
    procedurelen=len(procedure)
    j = 0
    while j<procedurelen:
        a = (procedure[j])[0]
        esql = r'''with q as (select b.specific_name ,a.routine_name ,(b.parameter_mode || ' ' || b.parameter_name || ' '|| b.data_type) as aa,a.routine_definition ,('CREATE OR REPLACE PROCEDURE public.'||a.routine_name || '(') as bb,(')LANGUAGE plpgsql AS $procedure$'||a.routine_definition|| '$procedure$;') as cc from information_schema.routines a inner join information_schema.parameters b on a.specific_name =b.specific_name where a.specific_schema ='public' and a.routine_definition is not null and a.routine_type ='PROCEDURE') select replace(replace((array_agg(distinct q.bb)::text||array_agg(distinct q.aa)::text||array_agg(distinct q.cc)::text),'{"',''),'"}','')from q where q.routine_name = '%s' group by q.routine_name;''' % a
        mycur.execute(esql)
        #mycur.execute("with q as (select b.specific_name ,a.routine_name ,(b.parameter_mode || ' ' || b.parameter_name || ' '|| b.data_type) as aa,a.routine_definition ,('CREATE OR REPLACE PROCEDURE public.'||a.routine_name || '(') as bb,(')LANGUAGE plpgsql AS $procedure$'||a.routine_definition|| '$procedure$;') as cc from information_schema.routines a inner join information_schema.parameters b on a.specific_name =b.specific_name where a.specific_schema ='public' and a.routine_definition is not null and a.routine_type ='PROCEDURE') select replace(replace((array_agg(distinct q.bb)::text||array_agg(distinct q.aa)::text||array_agg(distinct q.cc)::text),'{"',''),'"}','')from q where q.routine_name ='{}' group by q.routine_name".format(a))
        #mycur.execute("with q as (select b.specific_name ,a.routine_name ,(b.parameter_mode || ' ' || b.parameter_name || ' '|| b.data_type) as aa,a.routine_definition ,('CREATE OR REPLACE PROCEDURE public.'||a.routine_name || '(') as bb,(')LANGUAGE plpgsql AS $procedure$'||a.routine_definition|| '$procedure$;') as cc from information_schema.routines a inner join information_schema.parameters b on a.specific_name =b.specific_name where a.specific_schema ='public' and a.routine_definition is not null and a.routine_type ='PROCEDURE') select (array_agg(distinct q.bb)::text||array_agg(distinct q.aa)::text||array_agg(distinct q.cc)::text) from q where q.routine_name ='{}' group by q.routine_name".format(a))
        #mycur.execute("with q as (select b.specific_name ,a.routine_name ,(b.parameter_mode || ' ' || b.parameter_name || ' '|| b.data_type) as aa,a.routine_definition ,('CREATE OR REPLACE PROCEDURE public.'||a.routine_name || '(') as bb,(')LANGUAGE plpgsql AS $procedure$'||a.routine_definition|| '$procedure$;') as cc from information_schema.routines a inner join information_schema.parameters b on a.specific_name =b.specific_name where a.specific_schema ='public' and a.routine_definition is not null and a.routine_type ='PROCEDURE') select q.routine_name from q where q.routine_name ='{}' group by q.routine_name".format(a))
        (myresult,) = mycur.fetchone()
        f = open(path + './' + filename + './' + a + '.sql', 'w', encoding='utf-8')
        f.write(myresult)
        f.close()
        j += 1
if str=="函数":
    mycur.execute("select a.routine_name from information_schema.routines a inner join information_schema.parameters b on a.specific_name =b.specific_name where a.specific_schema ='public' and a.routine_definition is not null and a.routine_type ='FUNCTION' group by a.routine_name ")
    function=mycur.fetchall()
    functionlen=len(function)
    j = 0
    while j<functionlen:
        a = (function[j])[0]
        esql = r'''with q as (select b.specific_name ,a.routine_name ,(b.parameter_mode || ' ' || b.parameter_name || ' '|| b.data_type) as aa,a.routine_definition ,('CREATE OR REPLACE FUNCTION public.'||a.routine_name || '(')as bb,(')RETURNS '||a.type_udt_name || ' LANGUAGE plpgsql AS $function$' ||a.routine_definition || '$function$;') as cc from information_schema.routines a inner join information_schema.parameters b on a.specific_name =b.specific_name where a.specific_schema ='public' and a.routine_definition is not null and a.routine_type ='FUNCTION') select replace(replace(replace((array_agg(distinct q.bb)::text||array_agg(distinct q.aa)::text||array_agg(distinct q.cc)::text),'{"',''),'"}',''),'","',',')from q where q.routine_name = '%s' group by q.routine_name;''' % a
        mycur.execute(esql)
        (myresult,) = mycur.fetchone()
        f = open(path + './' + filename + './' + a + '.sql', 'w', encoding='utf-8')
        f.write(myresult)
        f.close()
        j += 1

运行pyinstaller -F ***.py将.py文件封装成.exe。