Pandas/SqlAlchemy inserted record is not shown by DAL

[clara]

Hi all, 

I have a web2py project in which I have used extensively pandas / sqlalchemy to read and write into the database. We have not created the models in web2py .

Now, I am migrating to using DAL. I have migrated all read operations to use DAL and I am now working with the write operations.

In doing so, I came accross a very weird issue (possibly DAL bug?):

Briefly if I do this:

- Write a row to a table with pd.to_sql (pandas with sqlalchemy connector to mysql)

- Read with DAL: the just added row is not returned. -> this is the issue. DAL does not show the just added row in the output query.

- Read with pd.read_sql: the just added row IS returned. (I can also see the new row in mysql workbench or any other tool)

I put this small example to reproduce this easily:

in db.py:

DB_STRING = "mysql+pymysql://usr:pass...@127.0.0.1:3306/test"
dal_conn = DAL(
DB_STRING,
pool_size=15,
migrate=False,
migrate_enabled=False,
check_reserved=["all"],
)

from sqlalchemy import create_engine
alchemy_conn = create_engine(DB_STRING)

in default.py controller:

def test_db():
query = "select * from students order by id desc"

df_alchemy = pd.read_sql(query, alchemy_conn)
df_dal = pd.DataFrame(dal_conn.executesql(query, as_dict=True))

row = pd.DataFrame(data =[['Charles', "CalTech"]], columns=['name', 'school'])
row.to_sql("students", alchemy_conn, if_exists='append', index=False)

df_alchemy_after_write = pd.read_sql(query, alchemy_conn)
df_dal_after_write = pd.DataFrame(dal_conn.executesql(query, as_dict=True))

return dict(df1_alchemy=df_alchemy.to_dict(orient='records'),
df1_dal=df_dal.to_dict(orient='records'),
df2_alchemy_after_write=df_alchemy_after_write.to_dict(orient='records'),
df2_dal_after_write=df_dal_after_write.to_dict(orient='records'))

I have a test database with a "students" table created via:

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
school VARCHAR(255) NOT NULL
);

INSERT INTO students (name, school) VALUES
('Alice', 'Harvard University'),
('Bob', 'Stanford University');

When I go to this controller in the browser , I get back the attached output. The record with id = 3 inserted shows with sqlalchemy connector but not with DAL connector.

I would really appreciate some explanation here. Sorry for the long write-up.

Thanks,

Clara

[Dave S]

On Monday, June 10, 2024 at 1:04:12 PM UTC-7 clara wrote:

Hi all, 

I have a web2py project in which I have used extensively pandas / sqlalchemy to read and write into the database. We have not created the models in web2py .

Now, I am migrating to using DAL. I have migrated all read operations to use DAL and I am now working with the write operations.

In doing so, I came accross a very weird issue (possibly DAL bug?):

Briefly if I do this:

- Write a row to a table with pd.to_sql (pandas with sqlalchemy connector to mysql)

- Read with DAL: the just added row is not returned. -> this is the issue. DAL does not show the just added row in the output query.

- Read with pd.read_sql: the just added row IS returned. (I can also see the new row in mysql workbench or any other tool)

 

[example elided]

 

When I go to this controller in the browser , I get back the attached output. The record with id = 3 inserted shows with sqlalchemy connector but not with DAL connector.

I would really appreciate some explanation here. Sorry for the long write-up.

Thanks,

Clara

I haven't worked with the DAL using executesql() directly, so I don't know if there are issues with that.  My first thoughts are that the panda writes may have missed a commit, but being able to see the new row in the workbench doesn't seem to support that idea.

Good luck.

/dps