[Tutor] how-to generate specific lines of text from two python lists

[Alan Gauld]

On 21/06/17 21:26, Tahir Hafiz wrote:

> My python skills are limited but I have managed to generate a couple
> of lists using python and the psycopg2 library by querying a postgress
> table and it's columns.

You say your python skills are limited but how about your SQL skills?
The reason I ask is that it sounds like you could greatly reduce the
amount of Python work by slightly increasing the SQL... Right tool
for the job etc.

You say you generated two lists from a postgres table and its columns? A
single table?

If that's the case you should be able to easily extract the two data
elements into a single list of tuples like (address,name).

Even if its more than one table you should still be able to do it
if there is any kind of logical link between the two pieces of data?

> I would like to use the two generated lists from the python script to
> create a file called upgrade_email_addresses.sql (and then later on
> use the psql "postgress cli" with the -f flag against this .sql file)
> which will be used to update the users database with the correct email
> addresses.

Why not just do the updates directly from Python? If you can do
a SELECT from Python you can also do the UPDATE. Just iterate
over the list of tuples generated above and execute an update
for each tuple.

> Is there a way to generate a file from two lists? 

Of course, and Peter has addressed that.
But I'd ask first whether you even need to do it?

> Any help would be much appreciated. I was thinking I could run the
> UPDATE queries in the psycopg2 console function directly in my python
> script but haven't been able to do that

I'm not sure what the console function is, but I'd think you
could run the UPDATEs directly from the dbapi with something
like:

for entry in data:
    cursor.execute(query, entry)

where data is your list of tuples and query is your
update statement.


If, for some reason, you cannot extract one list as a
set of tuples then you need to join the two lists, but
remember that SELECT does not return its results in
any order unless you specify an ORDER BY clause.
So your two lists may not match entry for entry.
Would that be an issue? How would you identify which
address goes with which name?

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos