Joining two lists for SQL string in Python

Question

I am currently joining two lists together in python to generate a string for input into a database using this code:

newDataString = ', '.join("%s=\'%s\'" % t for t in zip(tableColumns,data))

where tableColumns is a list of columns, and data is a list of data related to the columns. I am joining these together to perform an update.

It all works fine, except in cases where a value in the data list = None

cur.execute("UPDATE "+table+" SET "+str(newDataString)+" FROM "+tempTable+" WHERE "+generatePrimaryKeyMatches(primaryKey, table))
psycopg2.DataError: invalid input syntax for type date: "None"
LINE 1: ...ION RESEARCH & REP', suff='None', hon='None', dob='None', na...

I suppose I need an if statement in here somewhere to check every value in data before I put it into the join, and it should be treated differently (i.e. no \') if data = None, but I cannot for the life of me figure it out.

Any help greatly appreciated.

Hi Sundar I want to return the string as it would be in the case above, but without the apostrophes around it. e.g.: tableColumns = ['name', 'dob'] data = ['roger', None] would return: name='roger', dob=None Hope this clarifies. — Joe Plumb
– Joe Plumb, Commented May 12, 2014 at 12:21

jonrsharpe · Accepted Answer · 2014-05-12 12:38:14Z

1

I think this would be easier if you split it into two steps:

newDataList = []
for t in zip(tableColumns, data):
    if t[1] is not None:
        newDataList.append("{0}=\'{1}\'".format(*t))
    else:
        # ...deal with None
newDataString = ", ".join(newDataList)

Note I have switched the old-fashioned % formatting to str.format.

edited May 12, 2014 at 12:38

answered May 12, 2014 at 12:21

jonrsharpe

123k31 gold badges278 silver badges488 bronze badges

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

3 Comments

Joe Plumb Over a year ago

Hi John, this looks great. I've done a quick test and it looks like this may solve the problem. I'm just running a full test now so fingers crossed this is the last piece of the puzzle I need... if so... I owe you a beer. A huge cold beer. Will keep you posted...

Joe Plumb Over a year ago

Hi John, thank you so much for this, looks to have resolved the issue. One other query (if you could..!) I'm now having problems with strings that have apostrophes in them closing the data off early, for example...data='18221', datadesc='MANUFACTURE OF OTHER MEN'S OUTERWEA..., any idea how I could include str.replace into this functionality to remove all apostrophes from the data strings before writing to the newDataString? Best, Joe.

jonrsharpe Over a year ago

You could do for col, data in zip(...) then use .format(col, data.replace("'", "")) instead of simply data.

Collectives™ on Stack Overflow

Joining two lists for SQL string in Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related