this python script can be shortened/optimized, how?

Question

I'm not used to doing things the python way yet, but I'm almost certain the following script can be condensed. I'm not looking for speed optimization here, I'm looking for more readable code. Make it slower for all I care, but what are some ways to make this look more Python-esque.

I'm simply reading in a csv file filled with zipcodes and populating a database with 23 columns. Lots of typing. I also don't like hard coding things like the index of the row. Also, multis = '%s, '*23 followed by multis = multis[:-2] feels dirty.

Looking forward to see what some of you can come up with.

#!/usr/bin/python

import csv
import MySQLdb

db = MySQLdb.connect(host="localhost", user="root", db="test")
c = db.cursor()
f_csv = 'zip-codes-database-STANDARD.csv'
csvReader = csv.reader(open(f_csv))
ziplist = []
multis = '%s, '*23
multis = multis[:-2]
for row in csvReader:
    c.execute("""INSERT INTO lock_zipcodes_complete 
      (`zipcode`, `city`, `state`, `county`, `areacode`,
      `citytype`, `city_alias_abbreviation`,
      `city_alias_name`, `latitude`, `longitude`, `timezone`,
         `elevation`, `county_fips`, `dst`,
      `preferred_last_line_key`, `classification_code`,
      `multicounty`, `state_fips`, `city_state_key`,
         `city_alias_code`, `primary_record`, `city_mixed_case`,
      `city_alias_mixed_case`) VALUES(""" + multis + ')',
      (row[0], row[1], row[2], row[3], row[4], row[5], row[6],
      row[7], row[8], row[9], row[10], row[11], row[12],
      row[13], row[14], row[15], row[16], row[17], row[18],
      row[19], row[20], row[21], row[22]))

Answer 1

Please do not build SQL text like that. Please do not. Please.

First. The variable ziplist is not used. Delete it.

Second. Use real SQL binding.

c.execute( "INSERT...", row )

This is documented in the MySQLdb interface. http://mysql-python.sourceforge.net/MySQLdb-1.2.2/.

Answer 2

This part:

multis = '%s, '*23
multis = multis[:-2]

should be

multis = ', '.join(['%s'] * 23)

ziplist is not used, so you can just remove the line that sets it.

            (row[0], row[1], row[2], row[3], row[4], row[5], row[6],
            row[7], row[8], row[9], row[10], row[11], row[12],
            row[13], row[14], row[15], row[16], row[17], row[18],
            row[19], row[20], row[21], row[22])

should be just tuple(row) (for the specific way you're using it, just row will do).

The explicit list of column names in the INSERT is unpleasant, but needed if you're unsure whether that's all columns in that DB table or whether the order is right (or, of course, if you're sure that either is not the case); but that's a SQL - vs - CSV "impedence mismatch" issue rather that a Python one;-).

The whole loop:

for row in csvReader:
  c.execute(sql_statement, row)

can, optionally, further be compacted to:

c.executemany(sql_statement, csvReader)