CODEWITHSUNDEEP
pythonmysqlsqldatabaseinsert
user1464409
user1464409

Reputation: 1042

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

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?

Upvotes: 5

Views: 10071

Answers (2)

Ace.C
Ace.C

Reputation: 1271

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)

Upvotes: 1

Martijn Pieters
Martijn Pieters

Reputation: 1121972

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.

Upvotes: 7

Related Questions