Generate an SQL statement to insert multiple lines into a MySQL database at once using Python

Question

I am trying to generate a variable that I could use to insert multiple lines into a MySQL database or save to a file.

As I am new to python my mind is now buzzing with all the new concepts I am having to learn and I'm looking for a little reassurance that my approach is a good one.

The SQL syntax for inserting multiple items into a table is this:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');

This is the code I have:

import shelve

shelf = shelve.open('test.db', flag='r')

sql = "INSERT INTO 'myTableName' ( "
sql += ", ".join(shelf.itervalues().next())
sql = " ) VALUES "
for s in shelf: 

  sql += "( "
  sql += ', '.join(['\'%s\'' % ( value ) for (key, value) in shelf[s].items()])
  sql += " ),"

shelf.close()

print sql

It so nearly works (it has a trailing , instead of a ; on the final sql addition) but I'm sure there must be a more concise approach. Can you tell me what it is?

one hint, do not enclosed the tableName with a singlequotes as it is an Identifier and not a string literal. — John Woo
– John Woo, Commented Feb 5, 2013 at 16:21
JW, do you mean I should have "INSERT INTO myTableName ( " instead? — user1464409
– user1464409, Commented Feb 5, 2013 at 16:30
yes, or if you want to be safe use backticks, "INSERT INTO `tablename` (`column1`, `column2`) VALUES...." as well as for columnNames — John Woo
– John Woo, Commented Feb 5, 2013 at 16:32
Thank-you JW, I wasn't aware of this as I am only a beginner. I will add this to my growing list of things to read up about. — user1464409
– user1464409, Commented Feb 5, 2013 at 16:33
use backticks when needed, example when you are using reserved keywords, and your table or column name has space on it or something like this `hello-world` — John Woo
– John Woo, Commented Feb 5, 2013 at 16:37

Martijn Pieters · Accepted Answer · 2013-02-05 16:47:14Z

7

Don't generate SQL from string concatenation. Use SQL parameters instead:

cursor = connection.cursor()

cursor.executemany('INSERT INTO 'tablename' ('column1', 'column2') VALUES (%s, %s)',
        [sub.values() for sub in shelf.values()])

The database can then reuse the INSERT statement (it prepares a query plan for it), the database client layer will handle quoting for you, and you prevent SQL injection attacks to boot.

edited Feb 5, 2013 at 16:47

answered Feb 5, 2013 at 16:24

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

6 Comments

user1464409 Over a year ago

How would I save the statement it is using to a file using this method? I don't always want to execute the statement.

Martijn Pieters Over a year ago

@user1464409: Then just save the statement and execute it later; you really want to avoid inserting values if you can.

Kien Truong Over a year ago

I have read that MySQLdb use string interpolation to build the query before sending it so the server can't reuse the insert statement. Also using executemany should be faster.

Martijn Pieters Over a year ago

@Dikei: that would be a pity if MySQLdb did that, but at least it'd take care of the quoting correctly in that case. Updated to use executemany().

Martijn Pieters Over a year ago

@user1464409: I just edited the answer to use executemany() instead. :-)

|

Ace.C · Accepted Answer · 2020-03-12 05:43:29Z

Since the question specifically asked for how to generate a SQL insert statement, and not how to insert into a SQL database I present the following code:

def sterilize(s):
    if type(s) is str:
        return s.replace("'", "`").replace("\n", " ")
    else:
        return s

class insert_writer:
    def __init__(self, table_name, file_name, batch_size=42069):
        self.table_name = table_name
        self.file_name = file_name
        self.count = 0
        self.rows = 0
        self.batch_size = batch_size
        self.schema = []

    def __enter__(self):
        self.out_stream = open(self.file_name, "w")
        return self

    def __exit__(self, *args):
        self.out_stream.write(";\n")
        self.out_stream.close()

    def add_row(self, row_data):
        items = list(row_data.items())
        items.sort()
        keys = [x[0] for x in items]
        values = ["'%s'" % sterilize(x[1]) for x in items]
        output = ""
        if self.rows is 0:
            self.schema = keys

        if keys != self.schema:
            print(f"row {self.rows}: {keys} mismatches {self.schema}\n")

        if self.count is 0:
            output += ";\nINSERT INTO "
            output += self.table_name
            output += "(" + ", ".join(keys) + ") VALUES "
            output += "\n(" + ", ".join(values) + ")"
        else:
            output += ",\n(" + ", ".join(values) + ")"

        self.count = self.count + 1 if self.count < self.batch_size - 1 else 0
        self.rows += 1
        self.out_stream.write(output)

Collectives™ on Stack Overflow

Generate an SQL statement to insert multiple lines into a MySQL database at once using Python

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related