How to be more explicit when referencing Python objects in SQL

[davetapley]

Building on this example I'm often moving data from Python to DuckDB like this:

import pandas as pd
pandas_df = pd.DataFrame({'a': [42]})
duckdb.execute('CREATE TABLE IF NOT EXISTS tbl ...')
duckdb.execute('INSERT INTO tbl BY NAME (SELECT * FROM pandas_df)')

But I've been caught out a couple of times where my table and my DataFrame have the same name, e.g.:

people = pd.DataFrame({'a': [42]})
duckdb.execute('CREATE TABLE IF NOT EXISTS people ...')
duckdb.execute('INSERT INTO people BY NAME (SELECT * FROM people)')

I've only caught these through unit testing because it doesn't fail, it just inserts zero rows (presumably the query planner is picking people the table before people the Python variable).

Is there some existing way to be more explicit that I'm referencing the Python variable name and not the table name?

[davetapley]

See also:

• Variables can 'leak' through the stack in Python #11687

I've also found another awkward situation with a pattern I use sometimes:

@dataclass
class StoreData:
    data: DataFrame

    def store(self, con: DuckDBPyConnection):
        data = self.data
        con.execute('query using data')

Problem is tools like pylint and pyright complain that data is unused :\

[Mause]

There's a register method that could be helpful?

[davetapley]

There is? That sounds like just what I want! Where is it? 😁

[Tishj]

con.register(name, object)

[Tishj]

What it does under the hood is create a temporary view by that name so it's considered before other catalog entries

We are looking to change that to push CTEs instead, but that shouldnt affect anything :)