Executing a sql file in psycopg2

Question

More or less this function is part of a utility we use for our help desk technicians and is meant to provide them with an easy way to extract a table from a postgres backup, rename the table and reinsert it into another database. My function works great up until the actual insertion of the table into the chosen database, with cur.execute(). The sed(yes, I want to stick with sed) and extraction portions work great. I would like to execute the file somehow with psycopg2, but I am open to other suggestions if someone has an idea that uses subprocess and psql maybe. This function is called with a list of table names, a target database, and a sql file backup. I've seen other examples using .read(). I also know I should turn the filename into a variable to clean up the code a bit. If possible it also needs to work with python 2.6.

for tbl in selected_tables:
    # Open a file each iteration, dump the table in to it. Use .wait() to make sure the command finishes.
    table_file = open(str(tbl) + backup_suffix + ".sql", "w")
    p1 = subprocess.Popen(["/usr/bin/pg_restore", backup_picked, "-t", tbl], stdout=table_file)
    p1.wait()

    # Rename every instance of the table in the restore file. Format: _backup_YYYYMMDD
    sed_guts = "s/" + str(tbl) + "/"  + str(tbl) + backup_suffix + "/g"
    p2 = subprocess.Popen(["/bin/sed", "-i", sed_guts, str(tbl) + backup_suffix + ".sql"])
    p2.wait()
    table_file.close()

    # Restore the tables.
    try:
        # Gets the proper connection information. Works fine.
        site_config = site.ParseConfig(target_site)

        contents = open(str(tbl) + backup_suffix + ".sql", "r")


        con = psycopg2.connect(user=site_config['dbusername'],host=site_config['host'], \
                            password= site_config['password'], port=site_config['port'],\
                            dbname=site_config['dbname'])

        con.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

        # Connect to postgres or to the db and then execute the query.
        cur = con.cursor()
        # Fails here
        cur.execute(contents.read())
        os.remove(str(tbl) + backup_suffix + ".sql")

    except:
        os.remove(str(tbl) + backup_suffix + ".sql")

Shouldn't you close the table_file file after p1.wait() and before running sed on it? — Juan Fco. Roco, Feb 10 '15 at 01:08
Good thinking, I changed my code to reflect your suggestion but it had no impact on it failing it seems. — Minelr G, Feb 10 '15 at 16:56

score 0 · Accepted Answer · edited May 23 '17 at 11:49

In the end I ended up falling back on psql and subprocess.Popen with shell=True. This was not my preferred method and since I have other restrictions in place I am willing to go this route. If anyone is curious here's what I did.

backup_suffix = site.FileSuffix(backup_picked)

# Do the actual restoration here. Use sed to rename every instance in the files that will be created below.
# Make sure the created files get removed before finishing.
for tbl in selected_tables:
    table_file_name = str(tbl) + backup_suffix + ".sql"

    # Open a file each iteration, dump the table in to it. Use .wait() to make sure the command finishes.
    table_file = open(table_file_name, "w")
    p1 = subprocess.Popen(["/usr/bin/pg_restore", backup_picked, "-t", tbl], stdout=table_file)
    p1.wait()
    table_file.close()

    # Rename every instance of the table in the restore file. Format: _backup_YYYYMMDD
    sed_guts = "s/" + str(tbl) + "/"  + str(tbl) + backup_suffix + "/g"
    p2 = subprocess.Popen(["/bin/sed", "-i", sed_guts, table_file_name])
    p2.wait()

    # Use psql to restore the tables.
    try:
        site_config = site.ParseConfig(target_site)
        dontuse, site_config = addFlags(site_config, site_config)

        command = '/usr/bin/psql %s %s %s %s < %s' % (site_config['host'], site_config['dbusername'],
                                                       site_config['dbname'], site_config['port'], table_file_name)

        p2 = subprocess.Popen('%s' % command, shell=True)
        p2.communicate()[0]

        os.remove(table_file_name)

    except:
        os.remove(table_file_name)
        session.CleanUp()

    exit(0)

There are also multiple ways to write that many variables in a string. If anyone is curious here's a link to help: Using multiple arguments for string formatting in Python (e.g., '%s ... %s')

Here's another good resource for opening and closing files in Python: http://www.tutorialspoint.com/python/python_files_io.htm

Executing a sql file in psycopg2

1 Answers1