Not able to load tables from .sqlite database

[deepakpunia20]

Hi,

When I try to load data from table 'rain' using connectox, it is not able to identify the it but I am successfully able to do so using sqlite3.
Could you please let me know reason for it. I am using version - "connectorx-0.2.1a1"

[wangxiaoying]

Hi, can you try the absolute path of the database file? Here is an example of the connection string: sqlite:///home/user/path/test.db

[deepakpunia20]

Hi Wang,

My jupyter notebook is running in windows 10 environment and .sqlite file is placed at same location, where notebook runs. So, ideally absolute path is not required. Though, I tried with absolute path as well but 'AssertionError' is thrown. .sqlite file don't have any password.

May be you would like to give a try ? Link to download file is - https://media.moa-info-tg.kenya.precisionag.org/rain.sqlite

[wangxiaoying]

Can you try sqlite://C:\\...? In my above example, only /home/user/path/test.db part is the absolute path.

[deepakpunia20]

Hi Wang,

I tried but still it doesn't work. Could you please try to download .sqlite DB from URL mentioned in my earlier update and give it a try ? I have already tried out all possible ways but none works like - sqlite:///C:\... OR sqlite:////C:\... OR sqlite://////C:\...

[wangxiaoying]

Interesting, from the error message, it seems like it cannot recognize the file path correctly on windows. We will fix this issue. Thanks for reporting!

[wangxiaoying]

Hi @deepakpunia20 , can you try the newest alpha version pip install connectorx==0.2.5-alpha.1 and see whether it works? You may also need to urlencode your file path like this:

import urllib

path = urllib.parse.quote("C:\\Users\\Deepak Punia\\rain.sqlite")
df = cx.read_sql(f"sqlite://{path}", query)

[wangxiaoying]

That's weird. I will try to find a windows machine and see whether I can reproduce the error.

[wangxiaoying]

Hi @deepakpunia20 , I tried on a windows machine and still cannot reproduce your error. Here is the result with log printed:

with this script:

Can you make sure that the connectorx used in your notebook is the correct version (0.2.5a1)?

[deepakpunia20]

Hi @wangxiaoying ,

Could you please paste query that you ran. I will paste it as it is and try to run it. I am using correct version-

[deepakpunia20]

Hi @wangxiaoying ,

I tried to ran it from command prompt and I can see below log now:

C:\Users\Deepak Punia>python .\test_sqlite.py
sqlite://C%3A%5CUsers%5Crain.sqlite
[2022-03-09T04:21:27Z DEBUG connectorx::sources::sqlite] decoded conn: C:\Users\rain.sqlite
[2022-03-09T04:21:27Z DEBUG connectorx_python::pandas] Running dispatcher
[2022-03-09T04:21:27Z DEBUG connectorx::dispatcher] Fetching metadata
[2022-03-09T04:21:27Z DEBUG connectorx::sql] Transformed limit 1 query: SELECT count() FROM rain LIMIT 1
[2022-03-09T04:21:30Z DEBUG connectorx::dispatcher] Try get row rounts for entire result
[2022-03-09T04:21:30Z DEBUG connectorx::dispatcher] Manually count rows of each partitioned query and sum up
[2022-03-09T04:21:30Z DEBUG connectorx::sql] Transformed count query: SELECT count() FROM (SELECT count(*) FROM rain) AS CXTMPTAB_COUNT
[2022-03-09T04:21:32Z DEBUG connectorx::dispatcher] Allocate destination memory: 1x1
[2022-03-09T04:21:32Z DEBUG connectorx::dispatcher] Create destination partition
[2022-03-09T04:21:32Z DEBUG connectorx::dispatcher] Start writing
[2022-03-09T04:21:34Z DEBUG connectorx::dispatcher] Finalize partition 0
[2022-03-09T04:21:34Z DEBUG connectorx::dispatcher] Partition 0 finished
[2022-03-09T04:21:34Z DEBUG connectorx::dispatcher] Writing finished
Traceback (most recent call last):
File ".\test_sqlite.py", line 12, in
df = cx.read_sql(url, query)
File "C:\Users\Deepak_Punia\anaconda3\lib\site-packages\connectorx_init_.py", line 125, in read_sql
df = reconstruct_pandas(result)
File "C:\Users\Deepak_Punia\anaconda3\lib\site-packages\connectorx_init_.py", line 210, in reconstruct_pandas
placement=binfo.cids[0],
File "C:\Users\Deepak_Punia\anaconda3\lib\site-packages\pandas\core\internals\blocks.py", line 3041, in make_block
return klass(values, ndim=ndim, placement=placement)
File "C:\Users\Deepak_Punia\anaconda3\lib\site-packages\pandas\core\internals\blocks.py", line 1727, in init
super().init(values, placement, ndim)
File "C:\Users\Deepak_Punia\anaconda3\lib\site-packages\pandas\core\internals\blocks.py", line 1608, in init
placement = libinternals.BlockPlacement(placement)
File "pandas_libs\internals.pyx", line 45, in pandas._libs.internals.BlockPlacement.init
AssertionError

[deepakpunia20]

Hi @wangxiaoying ,

Looking at above log, it looked that connectorx version is not compatible with my pandas. I installed latest version of pandas and now, my query is running fine. I appreciate your prompt response to sort this issue.