I have a file that I download from the internet. It is in .xls format. I
need to save the data to an sql database. So I extract the data as follows:
import xlrd
wb = xlrd.open_workbook(file_contents=xls_file_name.read())
sh = wb.sheet_by_name('Sheet1')
data = [sh.row_values(r) for r in range(sh.nrows)]
engine = create_engine('mysql://blah:blah@localhost/blah')# Metadata is a Table
catalog.
metadata = MetaData()
hockey= Table(table_name, metadata, autoload=True, autoload_with=engine)
column_names = tuple(c.name for c in hockey.c)
final_data = [dict(zip(column_names, x)) for x in data]
ins = hockey.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
SQLALchemy error:
sqlalchemy.exc.OperationalError: (OperationalError) (1366, "Incorrect decimal
value: '' for column 'rtc_mvl' at row 44")
Table('hockey', MetaData(None), Column(u'team', MSString(length=16),
table=<hockey>, primary_key=True, nullable=False,
server_default=DefaultClause('', for_update=False)),Column(u'jersey_colour',
MSString(length=16), table=<hockey>),Column(u'stadium', MSString(length=32),
table=<hockey>),Column(u'goals', MSInteger(display_width=11),
table=<hockey>),Column(u'rtc_mvl', MSDecimal(precision=11, scale=1,
asdecimal=True),
table=<hockey>), Column(u'rtc_date', MSDate(), table=<hockey>,
primary_key=True, nullable=False,
server_default=DefaultClause('0000-00-00', for_update=False)))
Unfortunately, I have a column that can have empty/blank values. It is a
field that I define as a decimal in mysql database. When I attempt to
insert I obviously get an error described below. What I need to do is
remove the '' string when a cell is empty/blank. I haven't been able to
find examples of how I can do this on stackoverflow or in the xlrd
documentation. I tried to do something like the following but it does not
remove the '' string:
for item_index in range(0, len(data)):
item = data[item_index]
if item[5] == '':
item[5] == None
data[item_index] = item
I even tried to write the object to a csv file and re-load it to see the
value of the empty cells.
my_file = open('csv_temp.csv', 'wb')
writer = csv.writer(my_file)for rownum in xrange(len(file_csv)):
writer.writerow(file_csv[rownum])
my_data = []
my_file = open('csv_temp.csv', 'rb')
reader = csv.reader(myfile)for row in reader:
my_data.append(row)
Here is the row entry of my_data that has an empty cell (column 6 or list
index 5).
['green eggs and ham',
'milk chocolate',
'oranges and banana',
'01:05',
'-1.01',
'',
'2013-03-26',
'2']
You can see the '' field. How can I get SQLAlchemy to understand that '' is
a blank/null?
I appreciate the help.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
