daviszhen
commented
2 years ago it is similar to the jdbc compatibility
Closed dengn closed 2 years ago
daviszhen
commented
2 years ago it is similar to the jdbc compatibility
dengn
commented
2 years ago sqlalchemy support is added as 0.6 feature.
sukki37
commented
2 years ago @dengn can you make sure current priority on this?
badboynt1
commented
2 years ago sqlalchemy can be supported now. test script:
import sqlalchemy
from sqlalchemy import exists, Column, Integer, String, ForeignKey, DateTime, Text, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://dump:111@127.0.0.1:6001/motest?charset=utf8", echo=True)
print('Create connection complete')
database_name = "motest";
engine.execute("use " + database_name + ";")
print('Enter database successfully')
engine.execute("drop table if exists t1 ;")
engine.execute("create table t1(c1 int, c2 int) ;")
print('Create Table successfully')
DBSession = sessionmaker(bind=engine)
session = DBSession()
session.execute('insert into t1 values(1,1);')
session.execute('insert into t1 values(2,2);')
session.execute('insert into t1 values(3,3);')
print('insert successfully')
res = session.execute('select from t1')
all_res_list = res.fetchall()
print(all_res_list)
session.execute('delete from t1 where c1=1;')
print('delete successfully')
res = session.execute('select from t1')
all_res_list = res.fetchall()
print(all_res_list)
session.execute('update t1 set c2=10 where c1=2;')
print('update successfully')
res = session.execute('select * from t1')
all_res_list = res.fetchall()
print(all_res_list)
badboynt1
commented
2 years ago can not support create index statement and collation now. errors like:
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (20105, "not supported: statement: 'create index ix_tushare_test_df1_index on tushare_test.df1 (index)'")
/root/test/python/Flask-SQLAlchemy-RESTful-CRUD/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py:383: SAWarning: Exception attempting to detect unicode returns: ProgrammingError('(MySQLdb._exceptions.ProgrammingError) (1064, \'SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 53 near " CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1";\')') util.warn(
dengn
commented
2 years ago The demo code doesn't get passed:
nandeng-macbookpro:tushare nandeng$ python3 crud_test.py
Create connection complete
2022-11-04 14:10:38,205 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2022-11-04 14:10:38,206 INFO sqlalchemy.engine.Engine [raw sql] {}
Traceback (most recent call last):
File "/Users/nandeng/Test/tushare/crud_test.py", line 9, in
badboynt1
commented
2 years ago version will be changed to 8.0.30-MatrixOne-v0.6.0 to support this
dengn
commented
2 years ago SQL alchemy CRUD is more or less OK except for creating table with collate and charset.
Is there an existing issue for the same bug?
Environment
Actual Behavior
I wrote some python code to connect with some public dataset. I used sqlalchemy as the database connector. It works well with MySQL, but not with MatrixOne. It look like sqlalchemy cannot automatically create table with MatrixOne. Below is the error log of my python code.
Expected Behavior
MySQL log with this python code is
Steps to Reproduce