display error message if trying to use pandas integration with sqlalchemy 1.x

[edublancas]

jupysql supports sqlalchemy 1.x and 2.x; however, the most recent pandas release deprecated support for sqlalchemy 1.x

this breaks jupysql because users that install sqlalchemy 1.x will see errors (see #983) when trying to use the pandas integration with sqlalchemy 1.x if they have a recent pandas version:

AttributeError                            Traceback (most recent call last)
File <ipython-input-1-8ac0abc42964>:1
----> 1 get_ipython().run_line_magic('sql', '--persist df')

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2456, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2454     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2455 with self.builtin_trap:
-> 2456     result = fn(*args, **kwargs)
   2458 # The code below prevents the output from being displayed
   2459 # when using magics with decorator @output_can_be_silenced
   2460 # when the last Python token in the expression is a ';'.
   2461 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File ~/work/jupysql/jupysql/src/sql/magic.py:369, in SqlMagic.execute(self, line, cell, local_ns)
    261 @no_var_expand
    262 @needs_local_scope
    263 @line_magic("sql")
   (...)
    341 )
    342 def execute(self, line="", cell="", local_ns=None):
    343     """
    344     Runs SQL statement against a database, specified by
    345     SQLAlchemy connect string.
   (...)
    367 
    368     """
--> 369     return self._execute(
    370         line=line, cell=cell, local_ns=local_ns, is_interactive_mode=False
    371     )

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/ploomber_core/telemetry/telemetry.py:681, in Telemetry.log_call.<locals>._log_call.<locals>.wrapper(*args, **kwargs)
    679     injected_args = list(args)
    680     injected_args.insert(1, _payload)
--> 681     result = func(*injected_args, **kwargs)
    682 else:
    683     result = func(_payload, *args, **kwargs)

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/ploomber_core/exceptions.py:128, in modify_exceptions.<locals>.wrapper(*args, **kwargs)
    125 @wraps(fn)
    126 def wrapper(*args, **kwargs):
    127     try:
--> 128         return fn(*args, **kwargs)
    129     except (ValueError, TypeError) as e:
    130         _add_community_link(e)

    845         if_exists=if_exists,
    846         index=index,
    847         index_label=index_label,
    848         schema=schema,
    849         chunksize=chunksize,
    850         dtype=dtype,
    851         method=method,
    852         engine=engine,
    853         **engine_kwargs,
    854     )

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/pandas/io/sql.py:2851, in SQLiteDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
   2840             raise ValueError(f"{col} ({my_type}) not a string")
   2842 table = SQLiteTable(
   2843     name,
   2844     self,
   (...)
   2849     dtype=dtype,
   2850 )
-> 2851 table.create()
   2852 return table.insert(chunksize, method)

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/pandas/io/sql.py:984, in SQLTable.create(self)
    983 def create(self) -> None:
--> 984     if self.exists():
    985         if self.if_exists == "fail":
    986             raise ValueError(f"Table '{self.name}' already exists.")

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/pandas/io/sql.py:970, in SQLTable.exists(self)
    969 def exists(self):
--> 970     return self.pd_sql.has_table(self.name, self.schema)

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/pandas/io/sql.py:2866, in SQLiteDatabase.has_table(self, name, schema)
   2855 wld = "?"
   2856 query = f"""
   2857 SELECT
   2858     name
   (...)
   2863     AND name={wld};
   2864 """
-> 2866 return len(self.execute(query, [name]).fetchall()) > 0

File ~/work/jupysql/jupysql/.nox/test_unit_sqlalchemy_one/lib/python3.11/site-packages/pandas/io/sql.py:2673, in SQLiteDatabase.execute(self, sql, params)
   2671     raise TypeError("Query must be a string unless using sqlalchemy.")
   2672 args = [] if params is None else [params]
-> 2673 cur = self.con.cursor()
   2674 try:
   2675     cur.execute(sql, *args)

AttributeError: 'Connection' object has no attribute 'cursor'

fix:

when using the pandas + SQL integration (I believe the only place where we do this is in the --persist feature), we should check the sqlalchemy and pandas version. if the user has sqlalchemy 1.x with pandas <2.2.0, we should tell the user they should either upgrade to sqlalchemy 1.x or downgrade to pandas <2.2.0 (and provide example commands for both)