I'm working on this piece of code, python 2.6.6, old version because I will have to execute this script on linux servers(probably centOS 6, or Debian) with base repositories(stable) installed and no permission to install software that's not on these repos.
This snipped take care to select data from a database (mysql) with a certain schema(db structure), and insert it in an other database(postgresql) with a different schema.
cur_msql.execute("SELECT customerName, contactName, address, city, postal_code, country FROM tablename")
args_str = ','.join(cur_psql.mogrify("(%s,%s,%s,%s,%s,%s)", x) for x in cur_msql)
try:
cur_psql.execute("INSERT INTO tablename (name, contact, address, city, cap, country) \
VALUES " + args_str)
except psycopg2.Error as e:
print "Cannot execute that query", e.pgerror
sys.exit("Leaving early the script")
I get this error:
TypeError: 'dict' object does not support indexing
the following posts did not fixed my issue:
In the official website of psycopg2 I've found this, but I'm having bad times on understanding what it means:
Psycopg always require positional arguments to be passed as a sequence, even when the query takes a single parameter. And remember that to make a single item tuple in Python you need a comma! See Passing parameters to SQL queries.
I do think my problem is related to this :
cur_psql.mogrify needs positional parameters when you use it like that, but you can use named parameters like (%(customerName)s,%(contactName)s,%(address)s,%(city)s,%(postal_code)s,%(country)s)
With that said, instead of having python make a huge string of parameters, consider instead using cur_psql.executemany:
cur_psql.executemany("INSERT INTO tablename (name, contact, address, city, cap, country) VALUES (%(customerName)s,%(contactName)s,%(address)s,%(city)s,%(postal_code)s,%(country)s)", cur_msql)