316

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?

Improve this question
3
  • 77
    soulution for you: values = ",".join(map(str, value_list)) Commented Jun 4, 2012 at 11:55
  • it's good to understand the underlying reason: objects don't get automatically converted to their __repr__ (or __str__) outside of select built-in functions like print. or more precisely, those built-in functions are not automatically called in places where a string is the expected type. you have to really explicitly provide a string by converting them to one. Commented May 18, 2024 at 9:05
  • So if for "built-in functions like print", objects do "get automatically converted to their __repr__ (or __str__)", why not for join too? It is also built in, and it is a method of str, and what else could I possibly want from calling join on list of ints, but to obtain a string? It just feels like unnecessary extra writing to me ... but, eh, it is what it is. Commented Oct 10, 2024 at 10:38

8 Answers 8

Reset to default
588

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

Use this generator expression instead :

values = ','.join(str(v) for v in value_list)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Can also use .join(map(str, value_list))
103

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

values = ','.join(map(str, value_list))
Improve this answer

Comments

27

Replace 

values = ",".join(value_list)

with

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

OR

values = ','.join(str(value_list)[1:-1])
Improve this answer

3 Comments

Another one values = ','.join(str(value_list)[1:-1])
remove the [,] from your second example, a list comprehension is not required and by removing them you have a generator which is more efficient.
Actually, as explained at stackoverflow.com/questions/9060653/… , using a list instead of generator in the str.join() method is faster...
16

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.

Improve this answer

2 Comments

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.
No longer an issue in Python3, str('\xeb') => ë
4

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

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

Improve this answer

Comments

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)))
Improve this answer

Comments

1

solution:

    email = ''.join([str(nip), settings.ORGANISATION_EMAIL_SUFFIX])
Improve this answer

Comments

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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.