LemusThelroy LemusThelroy - 7 months ago 20
Python Question

Importing tuple list into Postgres via Python

I have a list of tuples that I want to upload into my database. I want to do this with one query so that I do not have to open up a connection whilst I parse over each tuple.

An example of my tuple list is as follows (this list will be considerably longer):
tuple_list = [(u'17',u'1', u'2', u'2'), (u'17',u'2', u'1', u'4')]

I want to write one query in postgres that will take this tuple_list and work through the list to populate a table called 'Predictions' in one call to the db.

A single call looks like this:

insert into 'Predictions' (userid, fixture_no, home_score, away_score) values (17, 1, 2, 2)


I have looked into converting my tuple into an XML file but wondered if there is a better way of doing this just in Postgres using the list of tuples?

If not, the XML file I have managed to produce looks like this...

<root>
<User_Id/>
<Fix_No/>
<Home_Score/>
<Away_Score/>
<User_Id>17</User_Id>
<Fix_No>1</Fix_No>
<Home_Score>2</Home_Score>
<Away_Score>2</Away_Score>
<User_Id>17</User_Id>
<Fix_No>2</Fix_No>
<Home_Score>1</Home_Score>
<Away_Score>4</Away_Score>
</root>


My main aim is to send all my predictions to the database in one shot rather than making numerous calls which will make my web application run slower.

Any thoughts or advice would be great!

Answer

You could use the multirow VALUES syntax described in the examples part of the postgresql documentation. Here ist a python snipped which creates the insert statement from the tuple_list from your question.

tuple_list = [(u'17',u'1', u'2', u'2'), (u'17',u'2', u'1', u'4')]
a = ["("+", ".join(a)+")" for a in tuple_list]
sql = "insert into 'Predictions' (userid, fixture_no, home_score, away_score) VALUES %s" % (",".join(a))
print(sql)

Output:

insert into 'Predictions' (userid, fixture_no, home_score, away_score) VALUES (17, 1, 2, 2),(17, 2, 1, 4)