SQL CE Database (.sdf) file connection with python

[MRAB]

On 2017-02-01 00:45, Jay Desai wrote:
> Hello,
>
>
> I came across a post from Nikhil Joshi at https://mail.python.org/pipermail/python-list/2014-November/681568.html.
>
> *.sdf database access - mail.python.org<https://mail.python.org/pipermail/python-list/2014-November/681568.html>
> mail.python.org
> On Saturday, April 21, 2012 6:55:55 AM UTC-4, Alex Willmer wrote: > On Apr 19, 9:18 pm, Page3D wrote: > > Hi, I am trying to connect and access ...
>
>
>
>
> I am facing the following problem. Can anyone please provide some guideline?
>
> Problem Statement: Extract data stored in the .sdf file to python. System config: Win 10 Pro 64-bit, python 3.5.2-64 bit, adodbapi library, SQL CE 3.5
>
> I am fairly new to programming and I have picked up Python as my first language to learn. Currently, I have hit a wall in process of connecting a SQL CE 3.5 .sdf file.
>
> I have used the adodbapi library. I have searched the web extensively over the past week to find a solution to this problem and to make sure that my connection string is correct. I have tried multiple options/solutions provided on stack overflow and https://www.connectionstrings.com/microsoft-sqlserver-ce-oledb-3-5/.
>
> Code:
>
> import adodbapi
>
> cons_str = "Provoider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.5;" \
>            "Data Source=D:\Work\Programming\Python\SQL_DataTransfer\LF.sdf;"\
>            "Persist Security Info=False;" \
>            "SSCE:Max Database Size=4091"
>
I can a problem here. Your string contains "Provoider", which is a 
misspelling. It should be "Provider".

It would also be a good idea to make those 'raw' strings (prefix them 
with 'r') where they contain backslashes.

> connection = adodbapi.connect(cons_str)
> print(connection)
>
> Error Message: Traceback (most recent call last): File "D:\Work\Programs\Python35.virtualenvs\sql_output\lib\site-packages\adodbapi\adodbapi.py", line 93, in make_COM_connecter c = Dispatch('ADODB.Connection') #connect after CoIninialize v2.1.1 adamvan NameError: name 'Dispatch' is not defined
>
> During handling of the above exception, another exception occurred:
>
> Traceback (most recent call last): File "D:\Work\Programs\Python35.virtualenvs\sql_output\lib\site-packages\adodbapi\adodbapi.py", line 112, in connect co.connect(kwargs) File "D:\Work\Programs\Python35.virtualenvs\sql_output\lib\site-packages\adodbapi\adodbapi.py", line 269, in connect self.connector = connection_maker() File "D:\Work\Programs\Python35.virtualenvs\sql_output\lib\site-packages\adodbapi\adodbapi.py", line 95, in make_COM_connecter raise api.InterfaceError ("Windows COM Error: Dispatch('ADODB.Connection') failed.") adodbapi.apibase.InterfaceError: Windows COM Error: Dispatch('ADODB.Connection') failed.
>
> During handling of the above exception, another exception occurred:
>
> Traceback (most recent call last): File "D:/Work/Programming/Python/SQL_DataTransfer/SQL_CE_reportDB.py", line 8, in connection = adodbapi.connect(cons_str) File "D:\Work\Programs\Python35.virtualenvs\sql_output\lib\site-packages\adodbapi\adodbapi.py", line 116, in connect raise api.OperationalError(e, message) adodbapi.apibase.OperationalError: (InterfaceError("Windows COM Error: Dispatch('ADODB.Connection') failed.",), 'Error opening connection to "Provoider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.5;Data Source=D:\Work\Programming\Python\SQL_DataTransfer\LF.sdf;Persist Security Info=False;SSCE:Max Database Size=4091"')
>
> At this point any help is much appreciated.
>
I've had a quick look on StackOverflow. I think you have to install pywin32.