297

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):
    try:
        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?

Homunculus Reticulli
  • 65,167
  • 81
  • 216
  • 341

8 Answers8

561

string.join connects elements inside list of strings, not ints.

Use this generator expression instead :

values = ','.join(str(v) for v in value_list)
cval
  • 6,609
  • 2
  • 17
  • 14
89

Although the given list comprehension / generator expression answers are ok, I find this easier to read and understand:

values = ','.join(map(str, value_list))
kirpit
  • 4,419
  • 1
  • 30
  • 32
22

Replace 

values = ",".join(value_list)

with

values = ','.join([str(i) for i in value_list])

OR

values = ','.join(str(value_list)[1:-1])
Priyank Patel
  • 3,495
  • 20
  • 20
  • 1
    Another one `values = ','.join(str(value_list)[1:-1])` – Priyank Patel Jun 04 '12 at 12:01
  • 4
    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 04 '12 at 12:01
  • 4
    Actually, as explained at http://stackoverflow.com/questions/9060653/list-comprehension-without-python , using a list instead of generator in the `str.join()` method is faster... – dtheodor Sep 04 '14 at 22:23
15

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])

by

values = ','.join([unicode(i) for i in value_list])

to be safe.

Community
  • 1
  • 1
Tomasz Nguyen
  • 2,561
  • 22
  • 25
  • 1
    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 '16 at 14:35
  • 2
    No longer an issue in Python3, `str('\xeb')` => `ë` – Brayoni Apr 30 '20 at 07:18
4

String interpolation is a nice way to pass in a formatted string.

values = ', '.join('$%s' % v for v in value_list)

Piet van Leeuwen
  • 319
  • 3
  • 12
3

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)))
Shaina Raza
  • 1,474
  • 17
  • 12
1

solution:

    email = ''.join([str(nip), settings.ORGANISATION_EMAIL_SUFFIX])
domi27
  • 6,903
  • 2
  • 21
  • 33
Gata
  • 361
  • 3
  • 4
0

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)
Sanket Suryawanshi
  • 79
  • 3