Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am attempting to insert data from a dictionary into a database. I want to iterate over the values and format them accordingly, depending on the data type. Here is a snippet of the code I am using:

def _db_inserts(dbinfo):
        rows = dbinfo['datarows']
        for row in rows:
            field_names = ",".join(["'{0}'".format(x) for x in row.keys()])
            value_list = row.values()
            for pos, value in enumerate(value_list):
                if isinstance(value, str):
                    value_list[pos] = "'{0}'".format(value)
                elif isinstance(value, datetime):
                    value_list[pos] = "'{0}'".format(value.strftime('%Y-%m-%d'))
            values = ",".join(value_list)
            sql = "INSERT INTO table_foobar ({0}) VALUES ({1})".format(field_names, values)
    except Exception as e:
        print 'BARFED with msg:',e
When I run the algo using some sample data (see below), I get the error:
  
TypeError: sequence item 0: expected string, int found
An example of a value_list data which gives the above error is:
value_list = [377, -99999, -99999, 'f', -99999, -99999, -99999, 1108.0999999999999, 0, 'f', -99999, 0, 'f', -99999, 'f', -99999, 1108.0999999999999, -99999, 'f', -99999, 'f', -99999, 'f', 'f', 0, 1108.0999999999999, -99999, -99999, 'f', 'f', 'f', -99999, 'f', '1984-04-02', -99999, 'f', -99999, 'f', 1108.0999999999999] 
What am I doing wrong?
string.join connects elements inside list of strings, not ints. 
Use this generator expression instead :
values = ','.join(str(v) for v in value_list)
Although the given list comprehension / generator expression answers are ok, I find this easier to read and understand:
values = ','.join(map(str, value_list))
                remove the [,] from your second example, a list comprehension is not required and by removing them you have a generator which is more efficient.
– jamylak
                Jun 4, 2012 at 12:01
                Actually, as explained at stackoverflow.com/questions/9060653/… , using a list instead of generator in the str.join() method is faster...
– dtheodor
                Sep 4, 2014 at 22:23
The answers by cval and Priyank Patel work great. However, be aware that some values could be unicode strings and therefore may cause the str to throw a UnicodeEncodeError error. In that case, replace the function str by the function unicode.
For example, assume the string Libië (Dutch for Libya), represented in Python as the unicode string u'Libi\xeb':
print str(u'Libi\xeb')
throws the following error:
Traceback (most recent call last):
  File "/Users/tomasz/Python/MA-CIW-Scriptie/RecreateTweets.py", line 21, in <module>
    print str(u'Libi\xeb')
UnicodeEncodeError: 'ascii' codec can't encode character u'\xeb' in position 4: ordinal not in range(128)
The following line, however, will not throw an error:
print unicode(u'Libi\xeb') # prints Libië
So, replace:
values = ','.join([str(i) for i in value_list])
values = ','.join([unicode(i) for i in value_list])
to be safe.
                This is the best solution here! values = ','.join([unicode(i) for i in value_list]) that works in case you have a mix of integers and strings with extended ascii characters.
– mel
                Jul 15, 2016 at 14:35
you can convert the integer dataframe into string first and then do the operation e.g.
df3['nID']=df3['nID'].astype(str)
grp = df3.groupby('userID')['nID'].aggregate(lambda x: '->'.join(tuple(x)))
Elements inside the list are not in the string format for this first we need to convert it to string then we can apply the join operation on that list as below :
summary = [ str(i) for i in summary ]
summery = " ".join(summary)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.