Feature: SQLAlchemy support

[alitrack]

Summary
Most BI developed in Python need SQLAlchemy to connect database, If Databend supports SQLAlchemy, will make it more popular.

for now, I tried connect databend with SQLAlchemy, got issues,

clickhouse+native://root:@127.0.0.1:9001/default

got,

number of values in row (1) differ from number of column processors (0)

mysql+mysqldb://root:@127.0.0.1:3307/default
or
mysql+mysqlconnector://root:@127.0.0.1:3307/default

got,

(MySQLdb._exceptions.OperationalError) (1105, 'Code: 1005, displayText = sql parser error: Expected an SQL statement, found: rollback.')

[wubx]

mysql+pymysql://root:@127.0.0.1:3307/default

[alitrack]

mysql+pymysql://root:@127.0.0.1:3307/default

got the same issue

[BohuTANG]

Fixed in #4735, and released in https://github.com/datafuselabs/databend/releases/tag/v0.7.11-nightly

It works now:

#!/usr/bin/python
# -*- coding: utf-8 -*-
# pip install sqlalchemy

import sqlalchemy

User = 'root'
Password = ''
Host = 'localhost'
Port = '3307'

# Creating the database connection

conn_str = 'mysql+pymysql://' + User + ':' + Password + '@' + Host \
    + ':' + Port + "/"
engine = sqlalchemy.create_engine(conn_str)
result = engine.execute('select * from system.contributors limit 4')
for row in result.fetchall():
    print(row)

result:

('Alex Chi',)
('Alkaid',)
('Andy Lok',)
('anglenet',)

[wubx]

#!/usr/bin/python
# -- coding: utf-8 --

pip install sqlalchemy

import sqlalchemy

User = 'root'
Password = ''
Host = 'localhost'
Port = '3307'

# Creating the database connection

conn_str = 'mysql+pymysql://' + User + ':' + Password + '@' + Host
+ ':' + Port + "/"
engine = sqlalchemy.create_engine(conn_str)
result = engine.execute('select * from system.tables limit 4')
for row in result.fetchall():
print(row)

Use jupyter run report error :

---------------------------------------------------------------------------
ResourceClosedError                       Traceback (most recent call last)
/var/folders/25/cl2wd5xd54sgh032z17qpc1w0000gn/T/ipykernel_20064/1986107328.py in <module>
     15     + ':' + Port + "/"
     16 engine = sqlalchemy.create_engine(conn_str)
---> 17 result = engine.execute('select * from system.tables limit 4')
     18 for row in result.fetchall():
     19     print(row)

<string> in execute(self, statement, *multiparams, **params)

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py in warned(fn, *args, **kwargs)
    388         if not skip_warning:
    389             _warn_with_version(message, version, wtype, stacklevel=3)
--> 390         return fn(*args, **kwargs)
    391 
    392     doc = func.__doc__ is not None and func.__doc__ or ""

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   3105 
   3106         """
-> 3107         connection = self.connect(close_with_result=True)
   3108         return connection.execute(statement, *multiparams, **params)
   3109 

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/base.py in connect(self, close_with_result)
   3164         """
   3165 
-> 3166         return self._connection_cls(self, close_with_result=close_with_result)
   3167 
   3168     @util.deprecated(

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events, _allow_revalidate)
     94                 connection
     95                 if connection is not None
---> 96                 else engine.raw_connection()
     97             )
     98 

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
   3243 
   3244         """
-> 3245         return self._wrap_pool_connect(self.pool.connect, _connection)
   3246 
   3247 

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   3210         dialect = self.dialect
   3211         try:
-> 3212             return fn()
   3213         except dialect.dbapi.Error as e:
   3214             if connection is None:

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/base.py in connect(self)
    305 
    306         """
--> 307         return _ConnectionFairy._checkout(self)
    308 
    309     def _return_conn(self, record):

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/base.py in _checkout(cls, pool, threadconns, fairy)
    765     def _checkout(cls, pool, threadconns=None, fairy=None):
    766         if not fairy:
--> 767             fairy = _ConnectionRecord.checkout(pool)
    768 
    769             fairy._pool = pool

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/base.py in checkout(cls, pool)
    423     @classmethod
    424     def checkout(cls, pool):
--> 425         rec = pool._do_get()
    426         try:
    427             dbapi_connection = rec.get_connection()

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/impl.py in _do_get(self)
    144             except:
    145                 with util.safe_reraise():
--> 146                     self._dec_overflow()
    147         else:
    148             return self._do_get()

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     68             self._exc_info = None  # remove potential circular references
     69             if not self.warn_only:
---> 70                 compat.raise_(
     71                     exc_value,
     72                     with_traceback=exc_tb,

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    205 
    206         try:
--> 207             raise exception
    208         finally:
    209             # credit to

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/impl.py in _do_get(self)
    141         if self._inc_overflow():
    142             try:
--> 143                 return self._create_connection()
    144             except:
    145                 with util.safe_reraise():

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/base.py in _create_connection(self)
    251         """Called by subclasses to create a new ConnectionRecord."""
    252 
--> 253         return _ConnectionRecord(self)
    254 
    255     def _invalidate(self, connection, exception=None, _checkin=True):

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/base.py in __init__(self, pool, connect)
    366         self.__pool = pool
    367         if connect:
--> 368             self.__connect()
    369         self.finalize_callback = deque()
    370 

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/pool/base.py in __connect(self)
    620             # init of the dialect now takes place within the connect
    621             # event, so ensure a mutex is used on the first run
--> 622             pool.dispatch.connect.for_modify(
    623                 pool.dispatch
    624             )._exec_w_sync_on_first_run(self.connection, self)

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/event/attr.py in _exec_w_sync_on_first_run(self, *args, **kw)
    327             with self._exec_once_mutex:
    328                 try:
--> 329                     self(*args, **kw)
    330                 except:
    331                     raise

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/event/attr.py in __call__(self, *args, **kw)
    341             fn(*args, **kw)
    342         for fn in self.listeners:
--> 343             fn(*args, **kw)
    344 
    345     def __len__(self):

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py in go(*arg, **kw)
   1689             once_fn = once.pop()
   1690             try:
-> 1691                 return once_fn(*arg, **kw)
   1692             except:
   1693                 if retry_on_exception:

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/create.py in first_connect(dbapi_connection, connection_record)
    672 
    673             try:
--> 674                 dialect.initialize(c)
    675             finally:
    676                 # note that "invalidated" and "closed" are mutually

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/base.py in initialize(self, connection)
   2949     def initialize(self, connection):
   2950         self._connection_charset = self._detect_charset(connection)
-> 2951         self._detect_sql_mode(connection)
   2952         self._detect_ansiquotes(connection)
   2953         self._detect_casing(connection)

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/base.py in _detect_sql_mode(self, connection)
   3394 
   3395     def _detect_sql_mode(self, connection):
-> 3396         row = self._compat_first(
   3397             connection.exec_driver_sql("SHOW VARIABLES LIKE 'sql_mode'"),
   3398             charset=self._connection_charset,

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/base.py in _compat_first(self, rp, charset)
   2869         inconsistencies."""
   2870 
-> 2871         row = rp.first()
   2872         if row:
   2873             return _DecodingRow(row, charset)

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/result.py in first(self)
   1066         """
   1067 
-> 1068         return self._only_one_row(
   1069             raise_for_second_row=False, raise_for_none=False, scalar=False
   1070         )

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/result.py in _only_one_row(self, raise_for_second_row, raise_for_none, scalar)
    556         onerow = self._fetchone_impl
    557 
--> 558         row = onerow(hard_close=True)
    559         if row is None:
    560             if raise_for_none:

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in _fetchone_impl(self, hard_close)
   1792 
   1793     def _fetchone_impl(self, hard_close=False):
-> 1794         return self.cursor_strategy.fetchone(self, self.cursor, hard_close)
   1795 
   1796     def _fetchall_impl(self):

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in fetchone(self, result, dbapi_cursor, hard_close)
    865 
    866     def fetchone(self, result, dbapi_cursor, hard_close=False):
--> 867         return self._non_result(result, None)
    868 
    869     def fetchmany(self, result, dbapi_cursor, size=None):

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in _non_result(self, result, default, err)
    916         # we only expect to have a _NoResultMetaData() here right now.
    917         assert not result._metadata.returns_rows
--> 918         result._metadata._we_dont_return_rows(err)
    919 
    920 

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in _we_dont_return_rows(self, err)
   1180 
   1181     def _we_dont_return_rows(self, err=None):
-> 1182         util.raise_(
   1183             exc.ResourceClosedError(
   1184                 "This result object does not return rows. "

/usr/local/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    205 
    206         try:
--> 207             raise exception
    208         finally:
    209             # credit to

ResourceClosedError: This result object does not return rows. It has been closed automatically.

use python3 file.py execute ok.

[alitrack]

three MySQL connector packages works on my PC, through notebook

and mysqlconnector got error when using ipython-sql

[BohuTANG]

Thank you @alitrack for the feedback.
We are still working on it to make it works more well :D