Cursor unable to find Pandas df registered in parent connection

[Alex-Monahan]

What does happen?
First, I create a connection to DuckDB in Python, then I register a Pandas dataframe on that connection. Then I create a cursor from that same connection and attempt to execute a SQL statement that refers to the Pandas dataframe I had just registered. However, I receive a "Catalog Error: Table with name main_table does not exist!". This behavior did not occur on 0.2.5.

What should happen?
Cursors made from a connection object should inherit the registered dataframes on that connection.

I do have a workaround where I can build the cursor first and then register the dataframes, but this still felt like a bug worth reporting!

To Reproduce

import pandas as pd
import duckdb

main_table = pd.DataFrame([{"join_column":"text"}])

sql = "select main_table.* from main_table"

try:
    duckdb_conn = duckdb.connect()
    duckdb_conn.execute("PRAGMA threads=4")
    duckdb_conn.register('main_table',main_table)
    cur = duckdb_conn.cursor()
    output_df = cur.execute(sql).fetchdf()
except Exception as err:
    print(err)
finally:
    duckdb_conn.close()
    
print(output_df)

Environment (please complete the following information):

• OS: Windows 10
• DuckDB Version 0.2.6

Before submitting

• Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
• Have you tried this on the latest master branch? In case you cannot compile, you may find some binaries here: https://github.com/duckdb/duckdb/releases/tag/master-builds
• (In this case the latest master branch on PyPi is the same as 0.2.6). I used pip install duckdb --pre --upgrade and no new version was installed. Looks like I'm pretty early on testing this version! :-)

[hannes]

Did you know that the cursor() call there is completely optional? You can directly call execute() on the duckdb_conn. Never understood this whole cursor business in the Python DB API..

[Alex-Monahan]

Ah, I did not know that! And it works fine with temp tables? I can test that before long. I think other databases won't persist temp tables unless you use a cursor instead of just a connection. Thanks! -Alex

…

On Sat, May 8, 2021, 11:13 AM Hannes Mühleisen ***@***.***> wrote: Did you know that the cursor() call there is completely optional? You can directly call execute() on the duckdb_conn. Never understood this whole cursor business in the Python DB API.. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1736 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMOORAJ6WHNYQWRNNZN5WX3TMV5NJANCNFSM44NSZFRQ> .

[hannes]

Yes should be no difference with temp tables.

[hannes]

In addition, the whole registration step should no longer be required with 0.2.6, there you can simply use the name of a data frame in your query and it should auto-register.

[Alex-Monahan]

Yes, I saw that! Definitely a great feature. I also really love the .parquet syntax. For us, we build up a Python dict of Dataframes and name them based on what our users name them, so we just have to loop through and register them with the right names. I will use the auto-registration in my analytical work though!

…

On Mon, May 10, 2021, 10:54 PM Hannes Mühleisen ***@***.***> wrote: In addition, the whole registration step should no longer be required with 0.2.6, there you can simply use the name of a data frame in your query and it should auto-register. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1736 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMOORAJGFE27V33M2DJW32TTNDBBTANCNFSM44NSZFRQ> .

[akdor1154]

@hannesmuehleisen would it be possible for cursor() to return the connection itself?

I know when using ddb it's not required, but people will run into this when using it the "normal" way you'd use a database with pandas:

ddb = duckdb.connect()

df = DataFrame()
ddb.register('my_table', df)

pd.read_sql('select * from my_table', ddb) # error, my_table doesn't exist

so it's a bit of a nasty introduction for newcomers.

If cursor() just returned self, is that a quick fix that would avoid this?

EDIT: oh i see, all well and good until someone calls cursor.close().

[omo]

Probably a duplicate of #1848.

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue was closed because it has been stale for 30 days with no activity.