Importing tuple list into Postgres via Python

Question

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!

salomonderossi · Accepted Answer · 2016-04-26T11:22:34.803

1

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)

# Insert into database (code from memory)
args_str = ','.join(cursor.mogrify("%s", (x, )) for x in tuple_list)
cursor.execute("INSERT INTO 'Predictions' (userid, fixture_no, home_score, away_score) VALUES "+args_str)

Output:

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

edited Apr 26 '16 at 11:22

answered Apr 26 '16 at 09:39

salomonderossi

2,180
14
20

I'm new to postgres so that comment is very helpful. As my list of tuples may have 5 tuples or even 50 tuples, how can I write a query that adapts to the number of tuples I am passing to it? – LemusThelroy Apr 26 '16 at 09:50
This is a fantastic answer. Thank you. If this query sits in my Python code, do you think it would be safe from SQL injection? As the tuple_list comes from user input from the web application. – LemusThelroy Apr 26 '16 at 10:37
@LemusThelroy You should ensure that all inserted data is properly escaped. The best way is to create stored procedures. This example ist NOT save! – salomonderossi Apr 26 '16 at 10:45
I have a stored procedure that I use which is as follows: select spupserttopredictions(%s, %s, %s, %s)" % (user_identity, Web_Fixture_No, Web_Home_Side_Score, Web_Away_Side_Score)) Can I still import the tuple list into this? – LemusThelroy Apr 26 '16 at 11:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/110269/discussion-between-lemusthelroy-and-salomonderossi). – LemusThelroy Apr 26 '16 at 12:42
@LemusThelroy This code is very bad practice. Check the correct and clean way: http://stackoverflow.com/a/30985541/131874 – Clodoaldo Neto May 04 '16 at 19:37
What makes it bad practice? Is it secure? – LemusThelroy May 04 '16 at 20:02

Importing tuple list into Postgres via Python

1 Answers1