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_lovel...@bigcorp.com',
> 'blaise_pas...@bigcorp.com', 'dennis_ritc...@bigcorp.com',
> 'linus_torva...@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_lovel...@bigcorp.com',
... 'blaise_pas...@bigcorp.com', 'dennis_ritc...@bigcorp.com',
... 'linus_torva...@bigcorp.com']
>>> for user, email in zip(listUsernames, listEmailaddress):
... print(user, email)
...
adal ada_lovel...@bigcorp.com
blaise_pas...@bigcorp.com
pascalb dennis_ritc...@bigcorp.com
ritchied linus_torva...@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_lovel...@bigcorp.com' WHERE username='adal';
> UPDATE users set email='blaise_pas...@bigcorp.com' WHERE
> username='pascalb'; UPDATE users set email='dennis_ritc...@bigcorp.com'
> WHERE username='ritchied'; UPDATE users set
> email='linus_torva...@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_lovel...@bigcorp.com',
'blaise_pas...@bigcorp.com',
'dennis_ritc...@bigcorp.com',
'linus_torva...@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_lovel...@bigcorp.com
pascalb, blaise_pas...@bigcorp.com
ritchied, dennis_ritc...@bigcorp.com
torvaldsl, linus_torva...@bigcorp.com
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
