Comments (4)
Thanks for the report. Can you include a full stack trace for the error being raised. Also, what is the output of type(connection)
after connection = engine.connect()
?
from pandas.
Pandas v2.2.2
sqlalchemy 1.4.52
this doesnt work:
from sqlalchemy import create_engine
uri = build_db_conn()
connection = create_engine(uri).connect()
print (uri, type(connection))
pandas.read_sql(sql, con = connection)
postgresql://****:****@localhost:34220/**** <class 'sqlalchemy.engine.base.Connection'>
/tmp/ipykernel_14167/2991965835.py:4: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
pandas.read_sql(sql, con = connection)
---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
Cell In[37], line 4
2 connection = create_engine(uri).connect()
3 print (uri, type(connection))
----> 4 pandas.read_sql(sql, con = connection)
File /opt/conda/lib/python3.11/site-packages/pandas/io/sql.py:706, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)
704 with pandasSQL_builder(con) as pandas_sql:
705 if isinstance(pandas_sql, SQLiteDatabase):
--> 706 return pandas_sql.read_query(
707 sql,
708 index_col=index_col,
709 params=params,
710 coerce_float=coerce_float,
711 parse_dates=parse_dates,
712 chunksize=chunksize,
713 dtype_backend=dtype_backend,
714 dtype=dtype,
715 )
717 try:
718 _is_table_name = pandas_sql.has_table(sql)
File /opt/conda/lib/python3.11/site-packages/pandas/io/sql.py:2738, in SQLiteDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)
2727 def read_query(
2728 self,
2729 sql,
(...)
2736 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
2737 ) -> DataFrame | Iterator[DataFrame]:
-> 2738 cursor = self.execute(sql, params)
2739 columns = [col_desc[0] for col_desc in cursor.description]
2741 if chunksize is not None:
File /opt/conda/lib/python3.11/site-packages/pandas/io/sql.py:2672, in SQLiteDatabase.execute(self, sql, params)
2670 raise TypeError("Query must be a string unless using sqlalchemy.")
2671 args = [] if params is None else [params]
-> 2672 cur = self.con.cursor()
2673 try:
2674 cur.execute(sql, *args)
AttributeError: 'Connection' object has no attribute 'cursor'
This also doesn't work:
pandas.read_sql(sql, build_db_conn())
---------------------------------------------------------------------------
ImportError Traceback (most recent call last)
Cell In[41], line 1
----> 1 pandas.read_sql(sql, build_db_conn())
File /opt/conda/lib/python3.11/site-packages/pandas/io/sql.py:704, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)
701 dtype_backend = "numpy" # type: ignore[assignment]
702 assert dtype_backend is not lib.no_default
--> 704 with pandasSQL_builder(con) as pandas_sql:
705 if isinstance(pandas_sql, SQLiteDatabase):
706 return pandas_sql.read_query(
707 sql,
708 index_col=index_col,
(...)
714 dtype=dtype,
715 )
File /opt/conda/lib/python3.11/site-packages/pandas/io/sql.py:903, in pandasSQL_builder(con, schema, need_transaction)
900 sqlalchemy = import_optional_dependency("sqlalchemy", errors="ignore")
902 if isinstance(con, str) and sqlalchemy is None:
--> 903 raise ImportError("Using URI string without sqlalchemy installed.")
905 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
906 return SQLDatabase(con, schema, need_transaction)
ImportError: Using URI string without sqlalchemy installed.
this works:
pandas.read_sql(sql, con = create_engine(build_db_conn()).connect().connection)
but gives warning:
/tmp/ipykernel_14167/3615795558.py:1: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
Looks like if isinstance(pandas_sql, SQLiteDatabase):
is failing to discover postgres?
from pandas.
@disarticulate your issue is unrelated. pandas 2.2+ requires sqlalchemy 2.0+ (see #57049). The OP shows that the version requirements are satisfied.
from pandas.
I think I may be missing some info here, but for starters it may be better to restructure your code.
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('starrocks://root:[email protected]:9030/default_catalog.ssb?charset=utf8')
def run_sql(sql: str) -> pd.DataFrame:
print("Query SQL is: " + sql)
with engine.connect() as connection:
df = pd.read_sql_query(sql, connection)
return df
this will ensure your connection is opened and closed when you are requesting data.
I wonder if it is an issue with the URI itself or the way you are passing the sql string as it works for me when I use it with my own database.
from pandas.
Related Issues (20)
- DOC: Development on Gitpod have problems HOT 1
- BUG: Ability to set both color and style in pandas plotting HOT 2
- Title: Feature Request: Improve diff Function to Support Forward and Backward CompletionENH: HOT 2
- Title: Feature Request: Improve diff Function to Support Forward and Backward CompletionENH: HOT 2
- Bug in Chunk Processing: Non-NULL IDs Become NULL During IterationBUG: HOT 2
- ENH: Add Float128 support for groupby. HOT 1
- PERF: Excessive memory consumption in pd.read_parquet HOT 3
- BUG: Resampling to `"B"` frequency with `closed="right"` and `label="right"` adds empty bins
- BUG: ArrowNotImplementedError: Unsupported cast from int64 to null using function cast_null HOT 1
- ENH: Adding `skipna:bool` to Rolling.sum HOT 2
- BUG: Replace fails after NaN in a Series of `string` HOT 2
- DEPR: future.no_silent_downcasting option HOT 1
- BUILD: Python Docker Build Issues HOT 4
- DOC: Incorrect Documentation: pd.Dataframe.dropna HOT 2
- BUG: Spurious `FutureWarning` when using `pd.read_json()` HOT 2
- BUG: read_json silently ignores the dtype when engine=pyarrow HOT 2
- ENH: support the Arrow PyCapsule Interface on pandas.Series (export) HOT 5
- BUG: HOT 3
- BUG: "Bad CRC-32 for file'docProps/core.xml" for Read large Excel file HOT 1
- BUG: Incorrect float32/float64 comparison result HOT 7
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pandas.