Giter Club home page Giter Club logo

Comments (4)

rhshadrach avatar rhshadrach commented on August 27, 2024

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.

disarticulate avatar disarticulate commented on August 27, 2024

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.

asishm avatar asishm commented on August 27, 2024

@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.

MotoMatt5040 avatar MotoMatt5040 commented on August 27, 2024

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)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo 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.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.