Pyramid models - can't understand the logic - noob

[Rosciuc Bogdan]

Hello all,

I don't quite get the models thing in Pyramid at the moment.

I was hopping that if you create a database on a database server you would be able to just make the app connect to the database and get the info from there without having to build a models.py file.

I tried using the reflection strategy from SQLAlchemy but it just left me with a bad taste as it's pretty complicated and it's pretty hard to get it working + someone told me that using reflection later (i.e: production) is a bad decision (did not explain but I guess performance or keeping the app safe from attacks?)

Now, I found a code generator, generated my models from a Postgresql server and tried to make them work ... but nothing happens and my forms don't get processed.

What I don't get is the whole initialize_db thing (why is it needed? what's the role of the whole thing?). 

Also I don't quite get the working flow: You get a .db file in your folder which has the info from your database ... is this file connecting to the database server when the app server starts? What's the point in having a separate database server anyway if you work two times (build tables on the db server, build tables in the models.py file)? 

At the moment i'm blocked with not being able to get my forms processed.

My project is at https://github.com/rbogdy/project/tree/master/aquameter if anyone can take a look maybe they can spot something.

Also if anyone could take the time to explain the "models" flow, I would really really appreciate it.

Thank you for reading this,

Bogdan

[Jonathan Vanasco]

most of these questions are more SqlAlchemy oriented, not about pyramid.

the initialize_db stuff is only needed when you're creating a database from scratch. many people don't do that.  there are a handful of SqlAlchemy oriented projects on PyPi that will build a "declarative" based models.py from an existing database.  I've made a few dozen projects with pyramid+sqlalchemy, I've only used an initialize_db script once.   This sort of stuff is more common in higher level projects like Django and Rails, which tend to automate database creation & migrations or are designed to minimize/remove developer interactions with the database.  pyramid gives you the choice to handle the database yourself if you want.

In SqlAlchemy, "reflection" allows you to create a class out of database connection and a table name.  I find this useful for small projects and quick fixes, but takes a little bit of overhead and isn't very extensible.  Some people don't like the performance on production systems.

Another approach to defining SqlAlchemy models is "declarative", where you declare a class and associate it to a table name. (http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative.html) You're then able to extend the class with various methods and relationships.  It's a lot easier to use.  Most people will use the "declarative" approach in their models.py folder.

Most people prefer using declarative.  The SqlAlchemy ORM tutorial is written using declarative (http://docs.sqlalchemy.org/en/latest/orm/tutorial.html)

In terms of "double work" -- initialize_db and migration scripts (using alembic) allow you to avoid writing the CREATE/ALTER sql statements.  They can generate the necessary statements based on the Python models.  The alternative is to write raw sql, optimize your database, and then create classes in model.py that map python to the database.  

[Rosciuc Bogdan]

It looks like I have to use the initialize_db script since the whole project is written from scratch.

So let me see if I got this right.

1) I create the database Metadata by using SqlAlchemy's declarative model. At this moment I might not have an already existing database

2) I connect this Metadata to my Postgresql server

3) I run the initialize_db script and this checks whether the tables exist or not (if they don't exist they are created)

4) I add data to the database

Is this correct? What do I do when I already created the tables in the postgresql server? should I delete them?

Thanks for your answer and thanks again for reading this. 

[tonthon]

Le 25/09/2014 18:53, Rosciuc Bogdan a écrit :
> It looks like I have to use the initialize_db script since the whole
> project is written from scratch.
>
> So let me see if I got this right.
>
> 1) I create the database Metadata by using SqlAlchemy's declarative
> model. At this moment I might not have an already existing database
> 2) I connect this Metadata to my Postgresql server
> 3) I run the initialize_db script and this checks whether the tables
> exist or not (if they don't exist they are created)
> 4) I add data to the database
>
> Is this correct? What do I do when I already created the tables in the
> postgresql server? should I delete them?

You've got three alternatives :

1- Use something like sqlautocode to dynamically build models regarding
your existing database structure
2- Migrate tables to fit your declared models
3- Delete your tables

You'll use 1 and 2 if you'd like to keep the datas stored in the
existing tables.

> --
> You received this message because you are subscribed to the Google
> Groups "pylons-discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to pylons-discus...@googlegroups.com
> <mailto:pylons-discus...@googlegroups.com>.
> To post to this group, send email to pylons-...@googlegroups.com
> <mailto:pylons-...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/pylons-discuss.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

On Thursday, September 25, 2014 1:21:33 PM UTC-4, tonthon wrote:

1- Use something like sqlautocode to dynamically build models regarding
your existing database structure
2- Migrate tables to fit your declared models
3- Delete your tables

You'll use 1 and 2 if you'd like to keep the datas stored in the
existing tables.

I assume you know SQL and are comfortable with it, since you already created tables in Postgres -- so I think you'll probably have an easier time going with method 1 or 2.  

initializedb is really for people who are not comfortable with sql, and would prefer a "hook" where they can write everything in Python.  

since Pyramid uses SqlAlchemy (or ZODB), you don't need to use Pyramid when dealing with your models. this might sound weird, but in things like Django and Rails, the ORM is part of the framework -- so you need to use framework when doing anything with the database.

if you don't include any pyramid code in your models.py (which most people recommend) then you can write maintenance tools, analytics, database population, etc only using small SqlAlchemy scripts and importing your models.py file