prom 0.9.26

An opinionated lightweight db orm for Postgres

An opinionated lightweight orm for Postgres

Example – Create a User class

Prom tries to make it as easy as possible on the developer to set common options, so you don’t have to constantly look at the documentation until you need to change something significant.

Here is how you would define a new Orm class:

# app.models (app/models.py)
import prom

class User(prom.Orm):

    schema = prom.Schema(
        "user_table_name", # the db table name
        username=prom.Field(str, True), # string field (required)
        password=prom.Field(str, True), # string field (required)
        email=prom.Field(str), # string field (not required)
        unique_user=('username',) # set a unique index on username field
        index_email=('email',) # set a normal index on email field
    )

You can specify the connection using a prom dsn url:

<full.python.path.InterfaceClass>://<username>:<password>@<host>:<port>/<database>?<options=val&query=string>#<name>

So to use the builtin Postgres interface on testdb database on host localhost with username testuser and password testpw:

prom.interface.postgres.Interface://testuser:testpw@localhost/testdb

To use our new User class:

# testprom.py
import prom
from app.models import User

prom.configure("prom.interface.postgres.Interface://testuser:testpw@localhost/testdb")

# create a user
u = User(username='foo', password='awesome_and_secure_pw_hash', email='[e-mail]')
u.set()

# query for our new user
u = User.query.is_username('foo').get_one()
print u.username # foo

# get the user again via the primary key:
u2 = User.query.get_pk(u.pk)
print u.username # foo

# let's add a bunch more users:
for x in xrange(10):
    username = "foo{}".format(x)
    ut = User(username=username, password="...", email="{}@bar.com".format(username))
    ut.set()

# now let's iterate through all our new users:
for u in User.query.get():
    print u.username

Environment Configuration

Prom can be automatically configured on import by setting the environment variable PROM_DSN:

export PROM_DSN=prom.interface.postgres.Interface://testuser:testpw@localhost/testdb

If you have multiple connections, you can actually set multiple environment variables:

export PROM_DSN_1=prom.interface.postgres.Interface://testuser:testpw@localhost/testdb1#conn_1
export PROM_DSN_2=prom.interface.postgres.Interface://testuser:testpw@localhost/testdb2#conn_2

After you’ve set the environment variable, then you just need to import Prom in your code:

import prom

and Prom will take care of parsing the dsn url(s) and creating the connection(s) automatically.

The Query class

You can access the query, or table, instance for each prom.Orm child you create by calling its .query class property:

print User.query # prom.Query

Through the power of magic, everytime you call this property, a new prom.Query instance will be created.

Customize the Query class

By default, Prom will look for a <name>Query class in the same module as your prom.Orm child, so, continuing the User example from above, if you wanted to make a custom UserQuery class:

# app.models (app/models.py)

class UserQuery(prom.Query):
    def get_by_emails(self, *emails):
        """get all users with matching emails, ordered by last updated first"""
        return self.in_email(*emails).desc_updated().get()

Now, we can further use the power of magic:

print User.query # app.models.UserQuery

And boom, we were able to customize our queries by just adding a class. If you want to explicitely set the class your prom.Orm child should use (eg, you want all your models to use random.module.CustomQuery which wouldn’t be auto-discovered by prom), you can set the query_class class property to whatever you want:

class DemoOrm(prom.Orm):
    query_class = random.module.CustomQuery

and then every instance of DemoOrm (or child that derives from it) will forever use random.module.CustomQuery.

Using the Query class

You should check the actual code for the query class in prom.query.Query for all the methods you can use to create your queries, Prom allows you to set up the query using psuedo method names in the form:

command_fieldname(field_value)

So, if you wanted to select on the foo fields, you could do:

query.is_foo(5)

or, if you have the name in the field as a string:

command_field(fieldname, field_value)

so, we could also select on foo this way:

name = 'foo'
query.is_field(name, 5)

The different WHERE commands:

• in – in_field(fieldname, *field_vals) – do a sql fieldname IN (field_val1, ...) query

• nin – nin_field(fieldname, *field_vals) – do a sql fieldname NOT IN (field_val1, ...) query

• is – is_field(fieldname, field_val) – do a sql fieldname = field_val query

• not – not_field(fieldname, field_val) – do a sql fieldname != field_val query

• gt – gt_field(fieldname, field_val) – do a sql fieldname > field_val query

• gte – gte_field(fieldname, field_val) – do a sql fieldname >= field_val query

• lt – lt_field(fieldname, field_val) – do a sql fieldname < field_val query

• lte – lte_field(fieldname, field_val) – do a sql fieldname <= field_val query

The different ORDER BY commands:

• asc – asc_field(fieldname) – do a sql ORDER BY fieldname ASC query

• desc – desc_field(fieldname) – do a sql ORDER BY fieldname DESC query

And you can also set limit and page in the get query:

query.get(10, 1) # get 10 results for page 1 (offset 0)
query.get(10, 2) # get 10 results for page 2 (offset 10)

They can be changed together:

# SELECT * from table_name WHERE foo=10 AND bar='value 2' ORDER BY che DESC LIMIT 5
query.is_foo(10).is_bar("value 2").desc_che().get(5)

You can also write your own queries by hand:

query.raw("SELECT * FROM table_name WHERE foo = %s", [foo_val])

The prom.Query has a couple helpful query methods to make grabbing rows easy:

• get – get(limit=None, page=None) – run the select query.

• get_one – get_one() – run the select query with a LIMIT 1.

• value – value() – similar to get_one() but only returns the selected field(s)

• values – values(limit=None, page=None) – return the selected fields as a tuple, not an Orm instance

  This is really handy for when you want to get all the ids as a list:

  # get all the bar ids we want
  bar_ids = Bar.query.select_pk().values()
  
  # now pull out the Foo instances that correspond to the Bar ids
  foos = Foo.query.is_bar_id(bar_ids).get()

• pk – pk() – return the selected primary key

• pks – pks(limit=None, page=None) – return the selected primary keys

• has – has() – return True if there is atleast one row in the db matching query

• get_pk – get_pk(pk) – run the select query with a WHERE _id = pk

• get_pks – get_pks([pk1, pk2,...]) – run the select query with WHERE _id IN (...)

• raw – raw(query_str, *query_args, **query_options) – run a raw query

• all – all() – return an iterator that can move through every row in the db matching query

• count – count() – return an integer of how many rows match the query

NOTE, Doing custom queries using raw would be the only way to do join queries.

The Iterator class

the get and all query methods return a prom.query.Iterator instance. This instance has a useful attribute has_more that will be true if there are more rows in the db that match the query.

Multiple db interfaces or connections

It’s easy to have one set of prom.Orm children use one connection and another set use a different connection, the fragment part of a Prom dsn url sets the name:

import prom
prom.configure("Interface://testuser:testpw@localhost/testdb#connection_1")
prom.configure("Interface://testuser:testpw@localhost/testdb#connection_2")

class Orm1(prom.Orm):
    connection_name = "connection_1"

class Orm2(prom.Orm):
    connection_name = "connection_2"

Now, any class that extends Orm1 will use connection_1 and any orm that extends Orm2 will use connection_2.

Using for the first time

Prom takes the approach that you don’t want to be hassled with installation while developing, so when it tries to do something and sees that the table doesn’t exist, it will use your defined prom.Schema for your prom.Orm child and create a table for you, that way you don’t have to remember to run a script or craft some custom db query to add your tables, Prom takes care of that for you automatically.

If you want to install the tables manually, you can create a script or something and use the install() method:

SomeOrm.install()

Schema class

The Field class

You can create fields in your schema using the Field class, the field has a signature like this:

Field(field_type, field_required, **field_options)

The field_type is the python type (eg, str or int or datetime) you want the field to be.

The field_required is a boolean, it is true if the field needs to have a value, false if it doesn’t need to be in the db.

The field_options are any other settings for the fields, some possible values:

• ref – a Foreign key strong reference to another schema

• weak_ref – a Foreign key weak reference to another schema

• size – the size of the field (for a str this would be the number of characters in the string)

• max_size – The max size of the field (for a str, the maximum number of characters, for an int, the biggest number you’re expecting)

• min_size – The minimum size of the field (can only be used with a corresponding max_size value)

• unique – set to True if this field value should be unique among all the fields in the db.

• ignore_case – set to True if indexes on this field should ignore case

Foreign Keys

You can have a field reference the primary key of another field:

s1 = prom.Schema(
    "table_1",
    foo=prom.Field(int)
)

s2 = prom.Schema(
    "table_2",
    s1_id=prom.Field(int, True, ref=s1)
)

the ref option creates a strong reference, which will delete the row from s2 if the row from s1 is deleted, if you would rather have the s1_id just set to None you can use the weak_ref option:

s2 = prom.Schema(
    "table_2",
    s1_id=prom.Field(int, weak_ref=s1)
)

Other things

Prom has a very similar interface to Mingo.

I built Prom because I didn’t feel like Python had a good “get out of your way” relational db orm that wasn’t tied to some giant framework or that didn’t try to be all things to all people, or that didn’t suck.

Prom is really super beta right now, built for First Opinion.

Prom assumes you want to do certain things, and so it tries to make those things really easy to do, while assuming you don’t want to do things like JOIN queries, so those are harder to do.

Versions

While Prom will most likely work on other versions, these are the versions we are running it on (just for references):

Python

$ python --version
Python 2.7.3

Postgres

$ psql --version
psql (PostgreSQL) 9.1.9

Installation

Prom currently requires psycopg2 since it only works with Postgres right now:

$ apt-get install libpq-dev python-dev
$ pip install psycopg

Then you can also use pip to install Prom:

$ pip install prom

License

MIT

Todo

Schema installation queries

You could do something like this:

s = prom.Schema(
  "table_name"
  field_name=(int, required_bool, options_dict),
  "CREATE INDEX foo ON table_name USING BTREE (field_name)",
  "INSERT INTO table_name (field_name) VALUES ('some value)"
)

That way you can do crazy indexes and maybe add an admin user or something. I don’t know if I Like the syntax, but it’s the best I’ve come up with to allow things like creating statement indexes on the month and day of a timestamp index for example.