SQL and CSV

[Peter Otten]

Nick wrote:

>         self.cursor = self.connection.cursor()
>         first = True
>         for row in reader:
>             if first:
>                 headers = []
>                 for r in row:
>                     n = r.strip().replace (' ', '_').replace ('-','_')
>                     headers.append (n)
>                 command = 'create table csv (%s)' % ','.join (headers)
>                 self.cursor.execute (command)
>                 first = False
>             else:
>                 command = 'insert into csv values ("%s")' % '","'.join
> (row)
>                 self.cursor.execute (command)
> 

You can simplify that a bit:

cursor = self.cursor = self.connection.cursor()

first_row = next(reader)
headers = [column.strip().replace(" ", "_").replace("-", "_") for column in 
first_row]
cursor.execute("create table csv (%s)" % ", ".join(headers))

placeholders = ", ".join("?"*len(headers))
command = "insert into csv values (%s)" % placeholders
cursor.executemany(command, reader)

While it may not matter here using placeholders instead of manually escaping 
user-provided values is a good habit to get into.

>         self.cursor.execute (self.query)
>         rows = self.cursor.fetchall()
  
rows = self.cursor.execute(self.query)

doesn't build an intermediate list.

>         i = 0
>         for row in rows:
>             results.add (row, i)
>             i = i + 1
 
This is written

for i, row in enumerate(rows):
    results.add(row, i)

in idiomatic Python.

Peter