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-26 11:22:34Z

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, 2016 at 11:22

answered Apr 26, 2016 at 9:39

salomonderossi

2,19814 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

LemusThelroy Over a year ago

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 Over a year ago

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.

salomonderossi Over a year ago

@LemusThelroy You should ensure that all inserted data is properly escaped. The best way is to create stored procedures. This example ist NOT save!

LemusThelroy Over a year ago

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 Over a year ago

Let us continue this discussion in chat.

|

Collectives™ on Stack Overflow

Importing tuple list into Postgres via Python

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related