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?
This question is related to
python
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))
Replace
values = ",".join(value_list)
with
values = ','.join([str(i) for i in value_list])
OR
values = ','.join(str(value_list)[1:-1])
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)))
String interpolation is a nice way to pass in a formatted string.
values = ', '.join('$%s' % v for v in value_list)
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.
Source: Stackoverflow.com