modin-project / modin-project/modin
read_sql - where * in
Nobody has claimed this yet.
- 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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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