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:password@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
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/c57200e9-3fe4-4bfb-a1e8-2243fb7c0cf5n%40googlegroups.com.
