3

I am currently working on moving data from one database to another in PostgreSQL. I am studying python with psycopg2 to connect and run SQL queries in one database. My problem is how can I pull and insert the data that I queried from one database to another. Most of the posts I found here talk about database from local server to a remote server. Like:

This suggests to use dblink or FDW. My situation is that both databases are in the same schema.

I am really new to this kind of task. Any suggestions?

Here's sample code in python that I am working with:

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
conn_t = p.connect("dbname='t_mig1' user='postgres' password='postgres' host='localhost'")

cur_p = conn_p.cursor()
cur_t = conn_t.cursor()

cur_t.execute("SELECT CAST(REGEXP_REPLACE(studentnumber, ' ', '') as integer), firstname, middlename, lastname FROM sprofile")
rows = cur_t.fetchall()

for row in rows:
    print "Inserting ", row[0], row[1], row[2], row[3]
    cur_p.execute(""" INSERT INTO "a_recipient" (id, first_name, middle_name, last_name) VALUES ('%s', '%s', '%s', '%s') """ % (row[0], row[1], row[2], row[3]))

cur_p.commit()
cur_pl.close()
cur_t.close()
Improve this question
5
  • 1
    both databases are in the same schema. Please clarify. And is this about a one-time transition, are repeated process or continuous replication? Commented May 3, 2018 at 3:19
  • apologies for the confusion. I have both db p_test and t_mig1 under public schema. I have 70 tables with 50 rows of data. Not sure what you mean by repeated process or continuous replication, I am really new to this kind of task. Commented May 3, 2018 at 4:57
  • Ok. it looks like both databases are in the same cluster in this case localhost:5432 Commented May 3, 2018 at 4:57
  • does that code work? it looks like it would? Commented May 3, 2018 at 4:59
  • It does. Basically, t_mig1 DB is a MySQL which I migrated to Postgresql. Problem is, the studentnumber is varchar while the id field in p_test is integer. I posted a thread on SO about this as I'm having problem with zeros. stackoverflow.com/questions/50146769/… Commented May 3, 2018 at 5:01

1 Answer 1

Reset to default
0

FDW and dblink have the advantage that they make a direct postgreql to postgresql connection which means that there will be no processing needed in your python code. here's an example that should work much like your example code, except faster.

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
strt="dbname='t_mig1' user='postgres' password='postgres' host='localhost'"
cur_p = conn_p.cursor();

cur_p.execute("select dblink_connect('tmpdb',%s);",(strt,));
cur_p.execute("""
  insert into "a_recipient" (id, first_name, middle_name, last_name)
  SELECT * FROM dblink('tmpdb',
    $$
    SELECT CAST(REGEXP_REPLACE(studentnumber, '[^0-9]', '','g') as integer), firstname, middlename, lastname FROM sprofile 
    $$ ) as x( a int,b text, c text,d text);
""");

cur_p.execute("SELECT dblink_disconnect('tmpdb');");

unfortunately I was not able to test this code. feel free to make any correction needed. I also changed the regex to remove all non digits.

Improve this answer
4
  • I will test this code Sir. Thank you. I haven't played with dblink but I read it a while ago. I think we need also to install the contrib in the server where our production db is located before I can use it. Right now, I am just testing in my local machine. Commented May 3, 2018 at 5:36
  • yeah, youll need contrib, and possibly create extension dblink; Commented May 3, 2018 at 5:39
  • Noted Sir. Question though about my code, will I get bottleneck with the execute() in my for loop? Commented May 3, 2018 at 5:41
  • probably your code will be faster than disk, it is hard to guess. the large number of inserts could be performace problem for postgresql. Commented May 3, 2018 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.