Thanks Alan and Peter,
Alan you are right this could be solved via an SQL statement but I was
asked to finish the python script.
Anyways, this worked and helped to solve the problem in the end:
# Create iterator object, dictionary which can be used to iterate against.
b_iter = iter(new_emails)
print "Creating a list of usernames and email addresses from retreived
database data:"
if __name__ == "__main__":
dictionary = dict(zip(usernames, new_emails))
my_list = []
for username in usernames:
my_list.append({'username':username, 'email':next(b_iter)})
print my_list
print
print "Creating a file called update_emails.sql with UPDATE statements from
the list."
# Open a file in write mode and write the UPDATE sql statements to the file
# Close the file once iterated against.
with open('update_emails.sql', 'w') as f:
for i in my_list:
mystring = "UPDATE users set email='{0}' WHERE username='{1}';"
new_mystring = mystring.format(i['email'], i['username'])
f.write(new_mystring + '\n')
f.close()
On Thu, Jun 22, 2017 at 9:03 AM, Alan Gauld via Tutor <tutor@python.org> wrote:
> 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
>
>
> _______________________________________________
> Tutor maillist - Tutor@python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
