[Re] Checking each item in m.group()?

[Peter Otten]

nospam at nospam.com wrote:

> I need to go through each line of a CSV file, and extract some fields
> using a regex. Then, I need to check each retrieved field, and if it
> looks like "", turn this into NULL so that it's correct SQL.

You are taking the wrong approach here.

Don't build SQL statements as strings; you are enabling the next SQL
injection attack. Pass parameters using the DB API instead.

Don't use regular expressions to parse a CSV file. Python's csv module is
more likely to deal correctly with the quirks of that standard.

A self-contained example:


import csv

import sqlite3 as sqlite
from cStringIO import StringIO

def records(infile):
    for row in csv.reader(infile):
        # replace empty strings in the second column with None
        # which translates to NULL in the database
        yield row[0], row[1] or None

def main():
    # create sample data; you may use a real file 
    infile = StringIO("""\
alpha,beta,gamma
zeta,,theta
""")

    # create the database
    db = sqlite.connect(":memory:")
    cursor = db.cursor()
    cursor.execute("create table demo (first, second);")

    # safely insert data
    cursor.executemany("insert into demo values (?, ?);", records(infile))

    # show contents
    for row in cursor.execute("select first, second, second is NULL "
                              "from demo order by first, second;"):
        print row

if __name__ == "__main__":
    main()

Peter