[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