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

Peter Otten __peter__ at web.de
Wed Jun 21 18:33:20 EDT 2017


Tahir Hafiz wrote:

> Hi All,
> 
> 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.
> 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.
> 
> Is there a way to generate a file from two lists? This is what I need to
> do ...
> 
> I have two lists such like:
> listUsernames = ['adal', '', 'pascalb', 'ritchied', 'torvaldsl', ... ]
> listEmailaddress = ['Ada_Lovelace at bigcorp.com',
> 'Blaise_Pascal at bigcorp.com', 'Dennis_Ritchie at bigcorp.com',
> 'Linus_Torvalds at bigcorp.com', ... ]

You can iterate over multiple lists with zip():

for user, email in zip(listUsernames, listEmailaddress):
    print(user, email)

However, you have to be very careful to keep them in sync:

>>> listUsernames = ['adal', '', 'pascalb', 'ritchied', 'torvaldsl']
>>> listEmailaddress = ['Ada_Lovelace at bigcorp.com',
... 'Blaise_Pascal at bigcorp.com', 'Dennis_Ritchie at bigcorp.com',
... 'Linus_Torvalds at bigcorp.com']
>>> for user, email in zip(listUsernames, listEmailaddress):
...     print(user, email)
... 
adal Ada_Lovelace at bigcorp.com
 Blaise_Pascal at bigcorp.com
pascalb Dennis_Ritchie at bigcorp.com
ritchied Linus_Torvalds at bigcorp.com

Oops, the empty string in listUsernames caused user and email address to get 
misaligned.

> So in my python script I would like to generate a text file
> (upgrade_email_addresses.sql) in the file system say in /tmp or /home,
> that will contain the following lines by perhaps looping against the
> lists in some way to create the lines in the external file:
> UPDATE users set email='Ada_Lovelace at bigcorp.com' WHERE username='adal';
> UPDATE users set email='Blaise_Pascal at bigcorp.com' WHERE
> username='pascalb'; UPDATE users set email='Dennis_Ritchie at bigcorp.com'
> WHERE username='ritchied'; UPDATE users set
> email='Linus_Torvalds at bigcorp.com' WHERE username='torvaldsl'; ....
> ....

Again you have to be very careful to make sure that all user-supplied data 
is properly quoted to defend against sql-injection attacks.

> 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 but now I'm thinking creating
> a upgrade_email_addresses.sql file and then calling psql cli against
> that would be easier.

Here's an example script using psycopg2:

$ cat psql_demo.py
import psycopg2

db = psycopg2.connect(database="foo", user="bar")

def show():
    cursor.execute("select username, email from users;")
    for row in cursor.fetchall():
        print(*row, sep=", ")

users = [
    'adal', 'pascalb', 'ritchied', 'torvaldsl'
]
emailaddresses = [
    'Ada_Lovelace at bigcorp.com',
    'Blaise_Pascal at bigcorp.com',
    'Dennis_Ritchie at bigcorp.com',
    'Linus_Torvalds at bigcorp.com'
]

cursor = db.cursor()

print("before update")
show()

cursor.executemany(
    "update users set email=%s where username=%s;",
    zip(emailaddresses, users)
)
db.commit()

print("after update")
show()

$ python3 psql_demo.py 
before update
adal, None
pascalb, None
ritchied, None
torvaldsl, None
after update
adal, Ada_Lovelace at bigcorp.com
pascalb, Blaise_Pascal at bigcorp.com
ritchied, Dennis_Ritchie at bigcorp.com
torvaldsl, Linus_Torvalds at bigcorp.com




More information about the Tutor mailing list