How to pass "null" values

[Mike]

Hi,

I am trying to transfer some data from a sqlite database to an MS SQL
database. I need to be able to pass null values for some of the empty
columns in the row. For example, I have something like this for my
row's format:

date | id | reg | ot | hol

The first two fields are required, but any or all of the last three
can be null. How do I tell SqlAlchemy that I want those to be null?
I've tried using None, an empty string, and the string
"DEFAULT" (which works for pymssql for whatever reason).

My class instantiation would look like this:

TimeEntries(data, id, reg, ot, hol, *args)

I am using the 0.5.0rc1 version of SqlAlchemy with Python 2.5.2 on
Windows XP. Any hints would be appreciated.

Thanks,

Mike

[Michael Bayer]

None or leave the paramter out of the INSERT entirely will work. Not
sure about specific quirks in MS-SQL drivers though as far as your
attempt of None not working, are you using pyodbc at least ?

[Mike]

I have pyodbc installed. I should have included the traceback I get
when I use "None". Sorry about that. Here it is:

Traceback (most recent call last):
File "\\debianis\loginscript$\PythonPackages\Development\Timesheet
\MCCB\mccb_db_converter.py", line 158, in <module>
ms_session.commit()
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\session.py", line 669, in commit
self.transaction.commit()
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\session.py", line 375, in commit
self._prepare_impl()
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\session.py", line 359, in _prepare_impl
self.session.flush()
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\session.py", line 1367, in flush
self._flush(objects)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\session.py", line 1437, in _flush
flush_context.execute()
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\unitofwork.py", line 263, in execute
UOWExecutor().execute(self, tasks)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\unitofwork.py", line 757, in execute
self.execute_save_steps(trans, task)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\unitofwork.py", line 772, in execute_save_steps
self.save_objects(trans, task)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\unitofwork.py", line 763, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\orm\mapper.py", line 1215, in _save_obj
c = connection.execute(statement.values(value_params), params)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\engine\base.py", line 848, in execute
return Connection.executors[c](self, object, multiparams, params)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\engine\base.py", line 899, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\engine\base.py", line 911, in _execute_compiled
self.__execute_raw(context)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\engine\base.py", line 920, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\engine\base.py", line 964, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File "c:\python25\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg
\sqlalchemy\engine\base.py", line 946, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('42000', '[42000] [Microsoft]
[ODBC SQL Server Driver][SQL Server]Error converting data type varchar
to numeric. (8114) (SQLExecDirectW)') u'INSERT INTO [tbl_TimeEntries]
(dateworked, empid, reg, ot, ce, hol, sklv, vac, ct, conv, misc,
comments) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' [u'12/25/2007',
'299', '', None, None, '6.5', None, None, None, None, None, None]


- Mike

[Michael Bayer]

On Oct 13, 2008, at 3:13 PM, Mike wrote:

> ProgrammingError: (ProgrammingError) ('42000', '[42000] [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Error converting data type varchar
> to numeric. (8114) (SQLExecDirectW)') u'INSERT INTO [tbl_TimeEntries]
> (dateworked, empid, reg, ot, ce, hol, sklv, vac, ct, conv, misc,
> comments) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' [u'12/25/2007',
> '299', '', None, None, '6.5', None, None, None, None, None, None]

the Nones are fine, its hosing out on your usage of '299' and/or '6.5'
for numeric values, when you're passing strings.

[Mike]

Hi,

I saw that, but thought it was something else for some reason. Anyway,
I set up a function to convert all my numeric stuff into floats and it
works now. Thanks...talking it out seemed to help!

Mike