modin-project / modin-project/modin

read_sql - where * in

Open
#2,802 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug 🦗 External P3 sql
Dominant language
Python
Stars
10.4k
Forks
677
PR merge metrics
No merged PRs in 30d

Description

Let's say I need to get all rows from a table where id is among a list of values (where id in [] syntax).
What is the correct syntax to do that right now?

DB: Postgres 13
Modin: master
Pandas: 1.2.1

query = "select * from table where id IN %(ids)s"
params = {'ids': [1, 2, 3]}
pd.read_sql(query, con, index_col=index, params=params)

Results in an error:

  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/modin/pandas/io.py", line 468, in read_sql
    return DataFrame(query_compiler=EngineDispatcher.read_sql(**kwargs))
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/modin/data_management/factories/dispatcher.py", line 152, in read_sql
    return cls.__engine._read_sql(**kwargs)
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/modin/data_management/factories/factories.py", line 131, in _read_sql
    return cls.io_cls.read_sql(**kwargs)
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/modin/engines/base/io/file_dispatcher.py", line 29, in read
    query_compiler = cls._read(*args, **kwargs)
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/modin/engines/base/io/sql/sql_dispatcher.py", line 61, in _read
    row_cnt = pandas.read_sql(row_cnt_query, con).squeeze()
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/pandas/io/sql.py", line 516, in read_sql
    chunksize=chunksize,
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/pandas/io/sql.py", line 1294, in read_query
    result = self.execute(*args)
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/pandas/io/sql.py", line 1162, in execute
    return self.connectable.execution_options().execute(*args, **kwargs)
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2235, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1003, in execute
    return self._execute_text(object_, multiparams, params)
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1178, in _execute_text
    parameters,
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1514, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/home/dshatz/dev/cointel/graphsense-postgres/append/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
KeyError: 'ids'

If this is supported, another question is:
Will read_sql also split the in condition array into smaller parts and distribute that across cores?

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with modin/engines/base/io/sql/sql_dispatcher.py, especially _read, and trace how pandas.read_sql receives the query and params through modin/pandas/io.py and the dispatcher and factory files shown in the traceback. Determine whether parameterized IN values reach both the row-count query and the data query, and whether the requested distribution across cores is supported; done means the reported query works and the intended partitioning behavior is covered.

Written by the indexing model from the issue text.

Assessment

Tech stack
pandas, postgresql, python, sqlalchemy
Domain
data, databases, distributed-systems
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.