CODEWITHSUNDEEP
pythonsql-serverms-accesspython-3.3pyodbc
AER
AER

Reputation: 1531

Issues with pyodbc numeric values being labelled as 'None' instead of NULL or empty

I am currently taking numeric values (amongst many other string and numeric values) from a set of access databases and uploading them to a single MS SQL Server database.

I am using 32-bit Python 3.3 and the respective pyodbc package.

I was wondering if there is a way to capture the fact that the numeric field is empty in the Access database without the driver returning the string 'None' instead*. The syntax used is as follows:

access_con = pyodbc.connect(connection_string)
access_cur = access_con.cursor()
access_SQL = 'SELECT * FROM ' + source_table
rows =  access_cur.execute(access_SQL).fetchall()
for row in rows:
    [Statement uploading each row to SQL Server using an INSERT INTO statement]

Any help would be appreciated; whether as a solution or as a more direct way to transfer the data.

*EDIT: 'None' is only a string because I turned it into one to add it to the INSERT INTO statement. Using row.replace('None','NULL') replaced all of the 'None' instances and replaced it with 'NULL' which was interpreted as a NULL value by the ODBC driver.

Upvotes: 2

Views: 4941

Answers (1)

Bryan
Bryan

Reputation: 17693

None is a Python object, not a string. It is the equivalent of NULL in SQL Server, or an "empty" column value in Access.

For example, if I have an Access table with the following definition:

Table Definition

That contains the following values (note that the first value of the Number column is empty):

Table Data

Relevant Python code produces:

...
>>> cursor = connection.cursor()
>>> rows = cursor.execute('select * from Table1').fetchall()
>>> print(rows)
[(None, ), (1, )]

This sample confirms the empty Access value is returned as None.

This PyODBC Documentation provides a good explanation of how ODBC and Python data types are mapped.

Upvotes: 3

Related Questions