2

I am trying to load table from a SQL Server CE database (.sdf file format) into Python (3.5.1). Here is what I have been playing around with:

import adodbapi
file="C:\\TS\\20160406_sdfPyt\\HC.sdf"
connstr = 'Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=%s;' %file
conn = adodbapi.connect(connstr)

This throws up error messages

Traceback (most recent call last):
File "C:\Users\TS\AppData\Local\Programs\Python\Python35\lib\site-packages\adodbapi\adodbapi.py", line 112, in connect
co.connect(kwargs)
File "C:\Users\TS\AppData\Local\Programs\Python\Python35\lib\site-packages\adodbapi\adodbapi.py", line 274, in connect
self.connector.Open()  # Open the ADO connection
File "<COMObject ADODB.Connection>", line 3, in Open
File "C:\Users\TS\AppData\Local\Programs\Python\Python35\lib\site-packages\win32com\client\dynamic.py", line 287, in _ApplyTypes_
result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Service Components', 'Format of the initialization string does not conform to the OLE DB specification.', None, 0, -2147217805), None)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "<pyshell#32>", line 1, in <module>
conn = adodbapi.connect(connstr)
File "C:\Users\TS\AppData\Local\Programs\Python\Python35\lib\site-packages\adodbapi\adodbapi.py", line 116, in connect
raise api.OperationalError(e, message)
adodbapi.apibase.OperationalError: (com_error(-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Service Components', 'Format of the initialization string does not conform to the OLE DB specification.', None, 0, -2147217805), None), 'Error opening connection to "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source==C:\\TSrinivas\\20160406_sdfPyt\\HC.sdf;"')

I tried including 

SSCE:Max Database Size=3999;Persist Security Info=True;

etc. in the connection string after searching online but with no luck. Could someone help me with the right connection string, or if I am doing anything else wrong here?

Edit: changed '==' to '=' in connstr

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tvns
  • 31
  • 1
  • 5
  • No a python expert, but why do you have: Data Source== and not Data Source= ? – ErikEJ Apr 09 '16 at 06:38
  • I tried both with == and = (still learning Python. I do most of my work in R and am using python only to read this sdf file). Edited my post. – tvns Apr 11 '16 at 17:10
  • I have been using pyodbc without any errors. – Walter_Ritzel Apr 11 '16 at 17:17
  • Looks like you need more """ around your connection string: connStr = """Provider=SQLOLEDB.1; User ID=%s; Password=%s; Initial Catalog=%s;Data Source= %s""" see: http://adodbapi.sourceforge.net/quick_reference.pdf – ErikEJ Apr 11 '16 at 17:25
  • @Walter_Ritzel I can not install pyodbc for some reason. Throws up this error error: Unable to find vcvarsall.bat – tvns Apr 11 '16 at 17:33
  • @ErikEJ tried adding """ but the same error remains. Do you think it is anything to do with my registry (read somewhere it could be CON/COM settings. I have no clue what it is) – tvns Apr 11 '16 at 17:34
  • I had this same problem. The quick solution I've found was to install pyodbc from wheel file. You can find the wheel file here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pyodbc – Walter_Ritzel Apr 11 '16 at 17:47
  • Thank you. The installation worked. Now I have another error. I changed my connection string to connstr = """Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\\TS\\20160406_sdfPyt\\HC.sdf;Persist Security Info=False;""" src_db_conn = pyodbc.connect(connstr) and this throws up the error: Traceback (most recent call last): File "", line 1, in src_db_conn = pyodbc.connect(connstr) pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') – tvns Apr 11 '16 at 19:14

1 Answers1

1

I reinstalled SQL Server CE 3.5 (first x86 and then x64) from https://www.microsoft.com/en-us/download/details.aspx?id=5783 and changed the connection string to 

connstr = """Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\TSrinivas\\20160406_sdfPyt\\HazardCache.sdf;"""

This works on a 4.0 database! I had tried this earlier but it had not worked, but somehow the re-installation did the job. I used adodbapi.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tvns
  • 31
  • 1
  • 5