On Wed, Mar 27, 2013 at 9:36 PM, <algotr8...@gmail.com> wrote:
> 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
>
If the above is literally what you wrote, then the problem is with this line:
> item[5] == None
You want a single "=", not "=="
Hope that helps,
Simon
--
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.
