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