0

I was asked to port a Access database to MySQL and provide a simple web frontend for the users. The DB consists of 8-10 tables and stores data about clients consulting (client, consultant,topic, hours, ...).

I need to provide a webinterface for our consultants to use, where they insert all this information during a session into a predefined mask/form.

My initial thought was to port the Access-DB to MySQL, which I have done and then use the web2py framework to build a user interface with login, inserting data, browse/scroll through the cases and pulling reports.

web2py with usermanagment and a few samples views & controllers and MySQL-DB is running. I added the DB to the DAL in web2py, but now I noticed, that with web2py it is mandatory to define every table again in web2py for it being able to communicate with the SQL-Server.

While struggeling to succesfully run the extract_mysql_models.py script to export the structure of the already existing SQL DB for use in web2py concerns about web2py are accumulating.

This double/redundant way of talking to my DB strikes me as odd and web2py does not support python3.

Is web2py the correct way to fulfill my task or is there better way?

Thank you very much for listening/helping out.

jlplenio
  • 115
  • 1
  • 9

1 Answers1

0

This double/redundant way of talking to my DB strikes me as odd and web2py does not support python3.

Any abstraction you want to use to communicate with your database (whether it be the web2py DAL, the Django ORM, SQLAlchemy, etc.) will have to have some knowledge of the database schema in order to construct queries.

Even if you programmatically generated all the SQL statements yourself without use of an ORM/DAL, your code would still have to have some knowledge of the database structure (i.e., somewhere you have to specify names of tables and fields, etc.).

For existing databases, we aim to automate this process via introspection of the database schema, which is the purpose of the extract_mysql_models.py script. If that script isn't working, you should report an issue on Github and/or open a thread on the web2py Google Group.

Also, note that when creating a new database, web2py helps you avoid redundant specification of the schema by handling migrations (including table creation) for you -- so you specify the schema only in web2py, and the DAL will automatically create the tables in the database (of course, this is optional).

Anthony
  • 25,466
  • 3
  • 28
  • 57
  • Thank you for your answer! My naive beginner knowledge hoped, that the DAL would, when connecting it to a DB, inspect and learn about it and its structure on its own. (crawl through the DB and build the models) Guess that is what the script is doing. And yes, I guess the best way is to let web2py create a new MySQL Database on its own and go from there. It's not mandatory, but it would be nice to implement the old data into the new DB. Should work if I rebuild the structure in web2py and then import. Maybe with the customization of the insert? – jlplenio Jan 27 '16 at 14:43
  • If you're going to specify all the table models in web2py, there's not particular reason you need to move the data from one MySQL database to another -- web2py will work fine connecting to the existing database. I was just pointing out that for projects where you are creating the database simultaneously with the web2py app, you only have to specify the schema once (in web2py), and web2py will handle creating the schema within the database for you. – Anthony Jan 27 '16 at 18:58
  • Actually, one reason you might consider migrating the data to a new database, is that by default, web2py uses a single auto-incrementing integer field as the primary key for every table. If your existing database doesn't take that approach, it might be worth making the change. web2py can handle alternative primary keys (including compound keys), but some functionality will be limited in that case. – Anthony Jan 27 '16 at 19:00
  • Also, note that you wouldn't want the web2py DAL to inspect your database on every HTTP request in order to derive the schema. Instead, you would want a one-time "offline" introspection of the schema in order for web2py to set up its models. Furthermore, you really want the models to be specified in application code that you can edit, because the models typically include metadata that is beyond the scope of the the database schema (e.g., validators, "represent" functions, etc.). This is the approach taken with the extract_mysql_models.py script. – Anthony Jan 27 '16 at 19:05
  • Finally, note that web2py (like most web application frameworks) is not meant to be a general purpose RDBMS admin tool (like MySQL Workbench), with the ability to connect to any existing database and enable full CRUD functionality without writing any code. Rather, its primary purpose is to enable the development of database-driven web applications. Something more automated (like MySQL Workbench) could be written on top of web2py, but web2py does not include such functionality out of the box (though it already comes pretty close with the model extraction script plus the built in grid). – Anthony Jan 27 '16 at 21:00