The Orator ORM provides a simple yet beautiful ActiveRecord implementation.

Related tags

ORMpythonormdatabase
Overview

Orator

Orator Build status

The Orator ORM provides a simple yet beautiful ActiveRecord implementation.

It is inspired by the database part of the Laravel framework, but largely modified to be more pythonic.

The full documentation is available here: http://orator-orm.com/docs

Installation

You can install Orator in 2 different ways:

  • The easier and more straightforward is to use pip
pip install orator

The different dbapi packages are not part of the package dependencies, so you must install them in order to connect to corresponding databases:

  • Postgres: psycopg2
  • MySQL: PyMySQL or mysqlclient
  • Sqlite: The sqlite3 module is bundled with Python by default

Basic Usage

Configuration

All you need to get you started is the configuration describing your database connections and passing it to a DatabaseManager instance.

from orator import DatabaseManager, Model

config = {
    'mysql': {
        'driver': 'mysql',
        'host': 'localhost',
        'database': 'database',
        'user': 'root',
        'password': '',
        'prefix': ''
    }
}

db = DatabaseManager(config)
Model.set_connection_resolver(db)

Defining a model

class User(Model):
    pass

Note that we did not tell the ORM which table to use for the User model. The plural "snake case" name of the class name will be used as the table name unless another name is explicitly specified. In this case, the ORM will assume the User model stores records in the users table. You can specify a custom table by defining a __table__ property on your model:

class User(Model):

    __table__ = 'my_users'

The ORM will also assume that each table has a primary key column named id. You can define a __primary_key__ property to override this convention. Likewise, you can define a __connection__ property to override the name of the database connection that should be used when using the model.

Once a model is defined, you are ready to start retrieving and creating records in your table. Note that you will need to place updated_at and created_at columns on your table by default. If you do not wish to have these columns automatically maintained, set the __timestamps__ property on your model to False.

Retrieving all models

users = User.all()

Retrieving a record by primary key

user = User.find(1)

print(user.name)

Querying using models

users = User.where('votes', '>', 100).take(10).get()

for user in users:
    print(user.name)

Aggregates

You can also use the query builder aggregate functions:

count = User.where('votes', '>', 100).count()

If you feel limited by the builder's fluent interface, you can use the where_raw method:

users = User.where_raw('age > ? and votes = 100', [25]).get()

Chunking Results

If you need to process a lot of records, you can use the chunk method to avoid consuming a lot of RAM:

for users in User.chunk(100):
    for user in users:
        # ...

Specifying the query connection

You can specify which database connection to use when querying a model by using the on method:

user = User.on('connection-name').find(1)

If you are using read / write connections, you can force the query to use the "write" connection with the following method:

user = User.on_write_connection().find(1)

Mass assignment

When creating a new model, you pass attributes to the model constructor. These attributes are then assigned to the model via mass-assignment. Though convenient, this can be a serious security concern when passing user input into a model, since the user is then free to modify any and all of the model's attributes. For this reason, all models protect against mass-assignment by default.

To get started, set the __fillable__ or __guarded__ properties on your model.

Defining fillable attributes on a model

The __fillable__ property specifies which attributes can be mass-assigned.

class User(Model):

    __fillable__ = ['first_name', 'last_name', 'email']

Defining guarded attributes on a model

The __guarded__ is the inverse and acts as "blacklist".

class User(Model):

    __guarded__ = ['id', 'password']

You can also block all attributes from mass-assignment:

__guarded__ = ['*']

Insert, update and delete

Saving a new model

To create a new record in the database, simply create a new model instance and call the save method.

user = User()

user.name = 'John'

user.save()

You can also use the create method to save a model in a single line, but you will need to specify either the __fillable__ or __guarded__ property on the model since all models are protected against mass-assignment by default.

After saving or creating a new model with auto-incrementing IDs, you can retrieve the ID by accessing the object's id attribute:

inserted_id = user.id

Using the create method

# Create a new user in the database
user = User.create(name='John')

# Retrieve the user by attributes, or create it if it does not exist
user = User.first_or_create(name='John')

# Retrieve the user by attributes, or instantiate it if it does not exist
user = User.first_or_new(name='John')

Updating a retrieved model

user = User.find(1)

user.name = 'Foo'

user.save()

You can also run updates as queries against a set of models:

affected_rows = User.where('votes', '>', 100).update(status=2)

Deleting an existing model

To delete a model, simply call the delete model:

user = User.find(1)

user.delete()

Deleting an existing model by key

User.destroy(1)

User.destroy(1, 2, 3)

You can also run a delete query on a set of models:

affected_rows = User.where('votes', '>' 100).delete()

Updating only the model's timestamps

If you want to only update the timestamps on a model, you can use the touch method:

user.touch()

Timestamps

By default, the ORM will maintain the created_at and updated_at columns on your database table automatically. Simply add these timestamp columns to your table. If you do not wish for the ORM to maintain these columns, just add the __timestamps__ property:

class User(Model):

    __timestamps__ = False

Providing a custom timestamp format

If you wish to customize the format of your timestamps (the default is the ISO Format) that will be returned when using the to_dict or the to_json methods, you can override the get_date_format method:

class User(Model):

    def get_date_format():
        return 'DD-MM-YY'

Converting to dictionaries / JSON

Converting a model to a dictionary

When building JSON APIs, you may often need to convert your models and relationships to dictionaries or JSON. So, Orator includes methods for doing so. To convert a model and its loaded relationship to a dictionary, you may use the to_dict method:

user = User.with_('roles').first()

return user.to_dict()

Note that entire collections of models can also be converted to dictionaries:

return User.all().serailize()

Converting a model to JSON

To convert a model to JSON, you can use the to_json method!

return User.find(1).to_json()

Query Builder

Introduction

The database query builder provides a fluent interface to create and run database queries. It can be used to perform most database operations in your application, and works on all supported database systems.

Selects

Retrieving all row from a table

users = db.table('users').get()

for user in users:
    print(user['name'])

Chunking results from a table

for users in db.table('users').chunk(100):
    for user in users:
        # ...

Retrieving a single row from a table

user = db.table('users').where('name', 'John').first()
print(user['name'])

Retrieving a single column from a row

user = db.table('users').where('name', 'John').pluck('name')

Retrieving a list of column values

roles = db.table('roles').lists('title')

This method will return a list of role titles. It can return a dictionary if you pass an extra key parameter.

roles = db.table('roles').lists('title', 'name')

Specifying a select clause

users = db.table('users').select('name', 'email').get()

users = db.table('users').distinct().get()

users = db.table('users').select('name as user_name').get()

Adding a select clause to an existing query

query = db.table('users').select('name')

users = query.add_select('age').get()

Using where operators

users = db.table('users').where('age', '>', 25).get()

Or statements

users = db.table('users').where('age', '>', 25).or_where('name', 'John').get()

Using Where Between

users = db.table('users').where_between('age', [25, 35]).get()

Using Where Not Between

users = db.table('users').where_not_between('age', [25, 35]).get()

Using Where In

users = db.table('users').where_in('id', [1, 2, 3]).get()

users = db.table('users').where_not_in('id', [1, 2, 3]).get()

Using Where Null to find records with null values

users = db.table('users').where_null('updated_at').get()

Order by, group by and having

query = db.table('users').order_by('name', 'desc')
query = query.group_by('count')
query = query.having('count', '>', 100)

users = query.get()

Offset and limit

users = db.table('users').skip(10).take(5).get()

users = db.table('users').offset(10).limit(5).get()

Joins

The query builder can also be used to write join statements.

Basic join statement

db.table('users') \
    .join('contacts', 'users.id', '=', 'contacts.user_id') \
    .join('orders', 'users.id', '=', 'orders.user_id') \
    .select('users.id', 'contacts.phone', 'orders.price') \
    .get()

Left join statement

db.table('users').left_join('posts', 'users.id', '=', 'posts.user_id').get()

You can also specify more advance join clauses:

clause = JoinClause('contacts').on('users.id', '=', 'contacts.user_id').or_on(...)

db.table('users').join(clause).get()

If you would like to use a "where" style clause on your joins, you may use the where and or_where methods on a join. Instead of comparing two columns, these methods will compare the column against a value:

clause = JoinClause('contacts').on('users.id', '=', 'contacts.user_id').where('contacts.user_id', '>', 5)

db.table('users').join(clause).get()

Advanced where

Sometimes you may need to create more advanced where clauses such as "where exists" or nested parameter groupings. It is pretty easy to do with the Orator query builder

Parameter grouping

db.table('users') \
    .where('name', '=', 'John') \
    .or_where(
        db.query().where('votes', '>', 100).where('title', '!=', 'admin')
    ).get()

The query above will produce the following SQL:

SELECT * FROM users WHERE name = 'John' OR (votes > 100 AND title != 'Admin')

Exists statement

db.table('users').where_exists(
    db.table('orders').select(db.raw(1)).where_raw('order.user_id = users.id')
)

The query above will produce the following SQL:

SELECT * FROM users
WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.id
)

Aggregates

The query builder also provides a variety of aggregate methods, ` such as count, max, min, avg, and sum.

users = db.table('users').count()

price = db.table('orders').max('price')

price = db.table('orders').min('price')

price = db.table('orders').avg('price')

total = db.table('users').sum('votes')

Raw expressions

Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points! To create a raw expression, you may use the raw() method:

db.table('users') \
    .select(db.raw('count(*) as user_count, status')) \
    .where('status', '!=', 1) \
    .group_by('status') \
    .get()

Inserts

Insert records into a table

db.table('users').insert(email='[email protected]', votes=0)

db.table('users').insert({
    'email': '[email protected]',
    'votes': 0
})

It is important to note that there is two notations available. The reason is quite simple: the dictionary notation, though a little less practical, is here to handle columns names which cannot be passed as keywords arguments.

Inserting records into a table with an auto-incrementing ID

If the table has an auto-incrementing id, use insert_get_id to insert a record and retrieve the id:

id = db.table('users').insert_get_id({
    'email': '[email protected]',
    'votes': 0
})

Inserting multiple record into a table

db.table('users').insert([
    {'email': '[email protected]', 'votes': 0},
    {'email': '[email protected]', 'votes': 0}
])

Updates

Updating records

db.table('users').where('id', 1).update(votes=1)

db.table('users').where('id', 1).update({'votes': 1})

Like the insert statement, there is two notations available. The reason is quite simple: the dictionary notation, though a little less practical, is here to handle columns names which cannot be passed as keywords arguments.

Incrementing or decrementing the value of a column

db.table('users').increment('votes')  # Increment the value by 1

db.table('users').increment('votes', 5)  # Increment the value by 5

db.table('users').decrement('votes')  # Decrement the value by 1

db.table('users').decrement('votes', 5)  # Decrement the value by 5

You can also specify additional columns to update:

db.table('users').increment('votes', 1, name='John')

Deletes

Deleting records

db.table('users').where('age', '<', 25).delete()

Delete all records

db.table('users').delete()

Truncate

db.table('users').truncate()

Unions

The query builder provides a quick and easy way to "union" two queries:

first = db.table('users').where_null('first_name')

users = db.table('users').where_null('last_name').union(first).get()

The union_all method is also available.

Read / Write connections

Sometimes you may wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE statements. Orator makes this easy, and the proper connections will always be used whether you use raw queries, the query builder or the actual ORM

Here is an example of how read / write connections should be configured:

config = {
    'mysql': {
        'read': {
            'host': '192.168.1.1'
        },
        'write': {
            'host': '192.168.1.2'
        },
        'driver': 'mysql',
        'database': 'database',
        'user': 'root',
        'password': '',
        'prefix': ''
    }
}

Note that two keys have been added to the configuration dictionary: read and write. Both of these keys have dictionary values containing a single key: host. The rest of the database options for the read and write connections will be merged from the main mysql dictionary. So, you only need to place items in the read and write dictionaries if you wish to override the values in the main dictionary. So, in this case, 192.168.1.1 will be used as the "read" connection, while 192.168.1.2 will be used as the "write" connection. The database credentials, prefix, character set, and all other options in the main mysql dictionary will be shared across both connections.

Database transactions

To run a set of operations within a database transaction, you can use the transaction method which is a context manager:

with db.transaction():
    db.table('users').update({votes: 1})
    db.table('posts').delete()

Note

Any exception thrown within a transaction block will cause the transaction to be rolled back automatically.

Sometimes you may need to start a transaction yourself:

db.begin_transaction()

You can rollback a transaction with the rollback method:

db.rollback()

You can also commit a transaction via the commit method:

db.commit()

By default, all underlying DBAPI connections are set to be in autocommit mode meaning that you don't need to explicitly commit after each operation.

Accessing connections

When using multiple connections, you can access them via the connection() method:

users = db.connection('foo').table('users').get()

You also can access the raw, underlying dbapi connection instance:

db.connection().get_connection()

Sometimes, you may need to reconnect to a given database:

db.reconnect('foo')

If you need to disconnect from the given database, use the disconnect method:

db.disconnect('foo')
Comments
  • MemoryError: stack overflow when I try to query a relation

    MemoryError: stack overflow when I try to query a relation

    I am running orator on windows/ python 2.7, and my model looks like:

    class Area(Model):
        pass
    
    class Restaurant(Model):
    
        @has_one()
        def area(self):
            return Area
    

    when i try to run r = Restaurant.find(1) r.id # works as normal r.area # throws "MemoryError: stack overflow" error

    bug 
    opened by tantita 26
  • If mysql begin a transaction and lost connection, reconnect

    If mysql begin a transaction and lost connection, reconnect

    Fix the following problems:

    Traceback (most recent call last):
      File "orator_db.py", line 97, in <module>
        with db.connection('yh_edu').transaction():
      File "/usr/lib/python3.6/contextlib.py", line 81, in __enter__
        return next(self.gen)
      File "/usr/lib/python3.6/site-packages/orator/connections/connection.py", line 288, in transaction
        self.begin_transaction()
      File "/usr/lib/python3.6/site-packages/orator/connections/mysql_connection.py", line 42, in begin_transaction
        self._connection.autocommit(False)
      File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 753, in autocommit
        self._send_autocommit_mode()
      File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 771, in _send_autocommit_mode
        self._read_ok_packet()
      File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 760, in _read_ok_packet
        pkt = self._read_packet()
      File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 991, in _read_packet
        packet_header = self._read_bytes(4)
      File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1037, in _read_bytes
        CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
    pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
    ```
    opened by zhanghaofei 17
  • ColumnDoesNotExist but not actually being referenced

    ColumnDoesNotExist but not actually being referenced

    Hi guys, have a strange issue I'm hoping for some assistance on.

    I have a migration: https://gist.github.com/ninjrdevelop/dd5f2715eeef30aca3308042880729a3

    When I run it, I get the error:

    [ColumnDoesNotExist]
      Column "permission_id" does not exist on table "users".
    

    migrate -vvv: https://gist.github.com/ninjrdevelop/6bb795742e1f736ff38360a3c89faa8a migrate --pretend: https://gist.github.com/ninjrdevelop/b1915ac62ae66f2f3d17052b3e04d864

    Image of table structure attached.

    Any thoughts? There's no reference to permission_id anywhere in this project. vivaldi_jdRDBd1nMR

    opened by ninjrdevelop 13
  • Incorrect datetime value: '2016-10-13 18:59:57.892599+00:00' for column 'created_at' at row 1

    Incorrect datetime value: '2016-10-13 18:59:57.892599+00:00' for column 'created_at' at row 1")

    Orator-ORM: 0.9.1 Python: 2.7.10 MySQL: 5.7.15

    It happened when I save a model.

    orator.exceptions.query.QueryException: (1292, "Incorrect datetime value: '2016-10-13 18:59:57.892599+00:00' for column 'created_at' at row 1") (SQL: INSERT INTOadmins(created_at,email,updated_at) VALUES (%s, %s, %s) ([<Pendulum [2016-10-13T18:59:57.892599+00:00]>, '[email protected]', <Pendulum [2016-10-13T18:59:57.892599+00:00]>]))

    The migration script:

    from orator.migrations import Migration
    
    
    class CreateAdminsTable(Migration):
    
        def up(self):
            """
            Run the migrations.
            """
            with self.schema.create('admins') as table:
                table.increments('id')
                table.string('email', 64).unique()
                table.json('profile').nullable()
                table.json('token').nullable()
                table.small_integer('status').default(0)
                table.timestamps()
    
        def down(self):
            """
            Revert the migrations.
            """
            self.schema.drop('admins')
    
    

    I guess the question is timezone info (+00:00).

    opened by damozhang 12
  • Keys_fix not defined

    Keys_fix not defined

    When I run your start up tutorial I get.

    Traceback (most recent call last): File "test.py", line 19, in <module> print(User.all()) File "/Library/Python/2.7/site-packages/orator/orm/model.py", line 544, in all instance = cls() File "/Library/Python/2.7/site-packages/orator/orm/model.py", line 121, in __init__ self._boot_if_not_booted() File "/Library/Python/2.7/site-packages/orator/orm/model.py", line 148, in _boot_if_not_booted klass._boot() File "/Library/Python/2.7/site-packages/orator/orm/model.py", line 167, in _boot cls._boot_columns() File "/Library/Python/2.7/site-packages/orator/orm/model.py", line 171, in _boot_columns connection = cls.resolve_connection() File "/Library/Python/2.7/site-packages/orator/orm/model.py", line 2758, in resolve_connection return cls.__resolver.connection(connection) File "/Library/Python/2.7/site-packages/orator/database_manager.py", line 43, in connection connection = self._make_connection(name) File "/Library/Python/2.7/site-packages/orator/database_manager.py", line 129, in _make_connection return self._factory.make(config, name) File "/Library/Python/2.7/site-packages/orator/connectors/connection_factory.py", line 36, in make return self._create_single_connection(config) File "/Library/Python/2.7/site-packages/orator/connectors/connection_factory.py", line 39, in _create_single_connection conn = self.create_connector(config).connect(config) File "/Library/Python/2.7/site-packages/orator/connectors/mysql_connector.py", line 31, in connect for key, value in keys_fix.items(): NameError: global name 'keys_fix' is not defined

    Seems like I'm following it correctly. Sorry if this is more tech support than an issue but I couldn't find any similar errors online elsewhere.

    opened by jamesh38 12
  • orator cli doesn't work on windows

    orator cli doesn't work on windows

    I tryed to execute orator make:migration create_example_table on my freshly created virtualenv, but it says that this command can't be executed. I also tryed .\Scripts\orator. The same thing. Steps that I followed:

    1. Created vitual enviroment
    2. Installed orator
    3. Activated my virtual env
    4. Created orator.xml
    5. Run orator make:migration create_example_table
    opened by yos-virtus 12
  • I keep getting orator.exceptions.query.QueryException

    I keep getting orator.exceptions.query.QueryException

    I have started using your awesome ORM on production and I get this error like once everyday which causes that endpoint to return a 502 until I refresh it like several times. Here is the stack-trace:

    Traceback (most recent call last):
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1836, in __call__
        return self.wsgi_app(environ, start_response)
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
        response = self.make_response(self.handle_exception(e))
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
        reraise(exc_type, exc_value, tb)
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
        response = self.full_dispatch_request()
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
        rv = self.handle_user_exception(e)
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
        reraise(exc_type, exc_value, tb)
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
        rv = self.dispatch_request()
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
        return self.view_functions[rule.endpoint](**req.view_args)
      File "./theapp/controllers.py", line 28, in restaurants
        ress = Restaurant.with_('area', 'foods').get().to_dict()
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/orator/orm/builder.py", line 198, in get
        models = self.get_models(columns)
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/orator/orm/builder.py", line 439, in get_models
        results = self.apply_scopes().get_query().get(columns)
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/orator/query/builder.py", line 1049, in get
        return self.get_fresh(columns)
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/orator/query/builder.py", line 1067, in get_fresh
        return self._processor.process_select(self, self._run_select())
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/orator/query/builder.py", line 1079, in _run_select
        not self._use_write_connection
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/orator/connections/connection.py", line 34, in _run
        e, query, bindings, wrapped
      File "/var/www/grub_flask/venv/local/lib/python2.7/site-packages/orator/connections/connection.py", line 318, in _try_again_if_caused_by_lost_connection
        raise QueryException(query, bindings, e)
    orator.exceptions.query.QueryException: (0, '') (SQL: SELECT * FROM `restaurants` ([]))
    [pid: 13958|app: 0|req: 185/967] 41.184.34.126 () {46 vars in 989 bytes} [Mon Mar 21 18:08:02 2016] GET /api/v1/restaurants/ => generated 0 bytes in 4 msecs (HTTP/1.1 500) 0 headers in 0 bytes (0 switches on core 0)
    

    Here is what my query looks like:

    Restaurant.with_('area', 'foods').get().to_dict()
    
    opened by tantita 10
  • Auto-Incrementing column that is not a primary key

    Auto-Incrementing column that is not a primary key

    I am looking to create a table that has an auto-incrementing integer that is not a primary key:

    with self.schema.create('contributors') as table:
          table.timestamp('updated_at').use_current()
          table.string('modified_by')
          table.integer('version_number').unisgned()
          table.string('record_id')
          table.integer('contributor_id', True)
          table.string('first_name')
          table.string('last_name')
          table.string('middle_initial')
          table.string('pseudonym')
    
          table.primary('record_id')
    
    

    In this example I want record_id to be my primary key and contributor_id to be auto-incrementing but NOT a primary key. When I run this as a migration using Postgres for my DB I get the following error:

    [QueryException]
    multiple primary keys for table "contributors" are not allowed
    (SQL: ALTER TABLE "contributors" ADD PRIMARY KEY ("record_id") (None))
    

    Removing the contributor_id column results in the migration being successfully run. How can I prevent autoincrement from making itself a key?

    opened by rohalla2 8
  • Weird error when loading Model classes, [Possible Bug?]

    Weird error when loading Model classes, [Possible Bug?]

    Hi, I'm trying to access an attribute of another Model (Say Model b) within my current Model with the relationship setting. But it gives me an error saying that the class b was not found, the reality is that they are at the same module and I imported the whole Model class. I tried in some way to find the root cause but not succeed, is that a possible bug? Or I'm using it in a wrong way? It's very much appreciated if anyone of you could provide any clue on this. Thank you! Below attaching the error message and code.

    - the code here:

    from orator import Model,SoftDeletes,accessor
    from orator.orm import belongs_to
    from Models.lottery_config import *
    
    class LotteryPlay(Model, SoftDeletes):
    
        __table__ = 'wcl_platform_lottery_play'
        __primary_key__ = 'plt_lotplay_id'
        __appends__ = ["lottery_name"]
    
        @belongs_to('plt_lotconf_id')
        def lottery_config(self):
            return LotteryConfig
    
        @accessor
        def lottery_name(self):
            return self.lottery_config.plt_lotconf_name
    

    - error message here:

    NameError
    name 'LotteryConfig' is not defined
    
    Traceback (most recent call last):
    
    File /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sanic/app.py, line 556, in handle_request
    
    response = await response
    
    File /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/coroutines.py, line 110, in __next__
    
    return self.gen.send(None)
    
    File /Users/tzhang/Documents/workspace/wclbackend/Blueprints/lottery_play.py, line 16, in get_all
    
    plays = get_all_plays(int(page))
    
    File /Users/tzhang/Documents/workspace/wclbackend/Services/services_lottery_play.py, line 12, in get_all_plays
    
    return make_operation_result(data=plays.serialize(),last_page=last_page)
    File /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/orator/pagination/length_aware_paginator.py, line 94, in serialize
    return self._items.serialize()
    File /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/backpack/collections/base_collection.py, line 771, in serialize
    return list(map(_serialize, self.items))
    File /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/backpack/collections/base_collection.py, line 765, in _serialize
    return value.serialize()
    File /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/orator/orm/model.py, line 2168, in serialize `
    ```
    question 
    opened by tonyzhangcn 8
  • ImportError: cannot import name DatabaseManager

    ImportError: cannot import name DatabaseManager

    I'm updating my issue.

    Now, instead of using Flask. I'm using a simple Pycnic framework with Orator. This is my installation

    • Virtualenv
    • Orator 0.9.3

    Somehow Orator suddenly cannot import all of the module. This is how I reproduce this issue :

    • Create a new virtual environment and clone my project here.
    • Install requirement pip install -r requirements.txt.
    • Run gunicorn gunicorn app:app --bind 0.0.0.0:8000 --reload this should be run well.
    • Change to branch dev-migration and run gunicorn again, it raised an error below
    [2016-12-29 14:05:43 +0000] [3230] [INFO] Starting gunicorn 19.6.0
    [2016-12-29 14:05:43 +0000] [3230] [INFO] Listening at: http://0.0.0.0:8000 (3230)
    [2016-12-29 14:05:43 +0000] [3230] [INFO] Using worker: sync
    [2016-12-29 14:05:43 +0000] [3235] [INFO] Booting worker with pid: 3235
    [2016-12-29 14:05:43 +0000] [3235] [ERROR] Exception in worker process
    Traceback (most recent call last):
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/local/lib/python2.7/site-packages/gunicorn/arbiter.py", line 557, in spawn_worker
        worker.init_process()
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/local/lib/python2.7/site-packages/gunicorn/workers/base.py", line 126, in init_process
        self.load_wsgi()
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/local/lib/python2.7/site-packages/gunicorn/workers/base.py", line 136, in load_wsgi
        self.wsgi = self.app.wsgi()
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/local/lib/python2.7/site-packages/gunicorn/app/base.py", line 67, in wsgi
        self.callable = self.load()
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/local/lib/python2.7/site-packages/gunicorn/app/wsgiapp.py", line 65, in load
        return self.load_wsgiapp()
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/local/lib/python2.7/site-packages/gunicorn/app/wsgiapp.py", line 52, in load_wsgiapp
        return util.import_app(self.app_uri)
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/local/lib/python2.7/site-packages/gunicorn/util.py", line 357, in import_app
        __import__(module)
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/hedwig/app.py", line 6, in <module>
        from handlers import client, messaging
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/hedwig/handlers/client.py", line 6, in <module>
        from models.client import Client as ClientModel
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/hedwig/models/client.py", line 1, in <module>
        from base import CrudBase, AuthenticableBase
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/hedwig/models/base.py", line 1, in <module>
        from db import db
      File "/home/imanovski/www/rimantoro/labs/python/venv_hedwig/hedwig/db.py", line 1, in <module>
        from orator import DatabaseManager
    ImportError: cannot import name DatabaseManager
    [2016-12-29 14:05:43 +0000] [3235] [INFO] Worker exiting (pid: 3235)
    [2016-12-29 14:05:43 +0000] [3230] [INFO] Shutting down: Master
    [2016-12-29 14:05:43 +0000] [3230] [INFO] Reason: Worker failed to boot.
    
    • Then I checkout to master branch and run the server it also raise same error, which should be fine previously

    Is there is known issue for using Orator within virtualenv ?

    Thanks.

    My previous comment :

    Hi,

    I'm installing orator via flask-orator and always raise ImportError when I try to run the server. Below is the trace

    /home/imanovski/www/rimantoro/labs/python/venv_flask/local/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.script is deprecated, use flask_script instead.
      .format(x=modname), ExtDeprecationWarning
    /home/imanovski/www/rimantoro/labs/python/venv_flask/local/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.migrate is deprecated, use flask_migrate instead.
      .format(x=modname), ExtDeprecationWarning
    /home/imanovski/www/rimantoro/labs/python/venv_flask/local/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.sqlalchemy is deprecated, use flask_sqlalchemy instead.
      .format(x=modname), ExtDeprecationWarning
    Traceback (most recent call last):
      File "manage.py", line 6, in <module>
        from application import create_app
      File "/home/imanovski/www/rimantoro/labs/python/venv_flask/projects/hedwig/application/__init__.py", line 22, in <module>
        from flask_orator import Orator
      File "/home/imanovski/www/rimantoro/labs/python/venv_flask/local/lib/python2.7/site-packages/flask_orator/__init__.py", line 4, in <module>
        from orator import DatabaseManager, Model as BaseModel
    ImportError: cannot import name DatabaseManager
    

    Where is DatabaseManager should come from ?

    I'm just following the Orator example and I thought this should be there inside Orator.

    opened by rimantoro 8
  • Relationship querying does not work [Orator 0.9.7]

    Relationship querying does not work [Orator 0.9.7]

    I have a basic setup, Survey -> has_many Questions

    Question -> has_many Choice Question -> belongs_to Survey

    Choice -> belongs_to Question

    DB migrations and relationships are set up, as usual, however when I am trying to query relationships, I am getting the following error:

    if not isinstance(related, basestring) and issubclass(related, Model): TypeError: issubclass() arg 1 must be a class

    Attached below the migration files and models. screen shot 2018-05-30 at 04 07 04 screen shot 2018-05-30 at 04 07 13 screen shot 2018-05-30 at 04 07 21 screen shot 2018-05-30 at 04 07 32 screen shot 2018-05-30 at 04 07 38 screen shot 2018-05-30 at 04 07 45 screen shot 2018-05-30 at 04 07 52

    opened by laith43d 7
  • query builder with sub query in FROM

    query builder with sub query in FROM

    I am having a issue with SQL with FROM clause like this how can I turn this into query builder. Can someone help me

    FROM tab1 AS t0 LEFT OUTER JOIN tab2 AS t1 ON t0.id = t1.id LEFT OUTER JOIN tab3 AS t2 ON t1.id = t2.id LEFT OUTER JOIN ( SELECT id, COUNT( member_id ) AS denominator FROM tab4 WHERE id = {id} (this is param) GROUP BY id ) as t3 on t0.id = t3.id

    opened by tuta2-rikkeisoft 0
  • docs: Fix a few typos

    docs: Fix a few typos

    There are small typos in:

    • orator/orm/model.py
    • orator/orm/relations/belongs_to_many.py
    • orator/orm/relations/morph_to_many.py
    • orator/query/builder.py
    • orator/schema/grammars/grammar.py

    Fixes:

    • Should read attachment rather than attachement.
    • Should read retrieve rather than retrive.
    • Should read minimum rather than minimun.
    • Should read function rather than fnction.
    • Should read definition rather than deifinition.
    • Should read callable rather than callbale.
    • Should read assignment rather than assigment.

    Semi-automated pull request generated by https://github.com/timgates42/meticulous/blob/master/docs/NOTE.md

    opened by timgates42 0
  • Command Cancelled! on running orator migrate command

    Command Cancelled! on running orator migrate command

    @sdispater Can you please check my question.

    db connection in the root

    from orator import DatabaseManager
    import environ
    env = environ.Env()
    environ.Env.read_env()
    
    config = {
        'mysql': {
            'driver': 'mysql',
            'host': 'localhost',
            'database':  env('DB_DATABASE'),
            'user': env('DB_USERNAME'),
            'password': env('DB_PASSWORD'),
            'prefix': ''
        }
    }
    
    db = DatabaseManager(config)
    
    

    migration file

    from orator.migrations import Migration
    
    
    class CreateProductsable(Migration):
    
        def up(self):
            """
            Run the migrations.
            """
            with self.schema.create('products') as table:
                table.increments('id')
                table.integer('category_id').unsigned()
                table.timestamps()
    
                table.foreign('category_id').references('id').on('jobs').on_delete('cascade')
    
        def down(self):
            """
            Revert the migrations.
            """
            self.schema.drop('products')
    

    model file

    from orator import Model
    
    
    class Product(Model):
    
        __table__ = 'products'
    
    opened by abubaker-417 1
  • Set the DB_DATABASE environment variable

    Set the DB_DATABASE environment variable

    I have installed orator in django Python 3.9.7 django 4.0.5

    I create a migration and model in the root that you can see my code and set connection but when i'm running migrate command orator migrate then coming these issues

    [ImproperlyConfigured]
      Set the DB_DATABASE environment variable
    migrate [-c|--config CONFIG] [-d|--database DATABASE] [-p|--path PATH] [-s|--seed] [--seed-path SEED-PATH] [-P|--pretend] [-f|--force]
    

    db connection in the root

    from orator import DatabaseManager, Schema
    import environ
    env = environ.Env()
    environ.Env.read_env()
    
    databases = {
        'mysql': {
            'driver': 'mysql',
            'host': 'localhost',
            'database':  env('DB_DATABASE'),
            'user': env('DB_USERNAME'),
            'password': env('DB_PASSWORD'),
            'prefix': ''
        }
    }
    db = DatabaseManager(databases)
    schema = Schema(db)
    

    migration file

    from orator.migrations import Migration
    
    
    class CreateProductsable(Migration):
    
        def up(self):
            """
            Run the migrations.
            """
            with self.schema.create('products') as table:
                table.increments('id')
                table.integer('category_id').unsigned()
                table.timestamps()
    
                table.foreign('category_id').references('id').on('jobs').on_delete('cascade')
    
        def down(self):
            """
            Revert the migrations.
            """
            self.schema.drop('products')
    

    model file

    from orator import Model
    
    
    class Product(Model):
    
        __table__ = 'products'
    

    Please can you help me about this issue where i'm doing mistake . Thanks in advance

    opened by abubaker-417 1
  • Datetime not working

    Datetime not working

    Receiving this issue: 'str' object has no attribute 'tzinfo'

    Model:

    class Review(Model):
        __table__ = 'reviews'
        __guarded__ = ['id', 'created_at', 'updated_at']
        __dates__ = ['reviewed_at']
    

    Creating:

    data = {'reviewed_at': '2021-08-07T09:45:20.000Z'}
    Review.create(data)
    

    The date seems to be in a parseable format to me. Not sure why I am getting this error.

    I have also tried other formats like 2021-10-02 09:44:04.000.

    opened by robertmylne 0
Releases(0.9.10)
  • 0.9.10(Sep 28, 2020)

  • 0.9.9(Jul 15, 2019)

    Fixed

    • Fixed missing relationships when eager loading multiple nested relationships.
    • Fixed a possible AttributeError when starting a transaction.
    • Fixed an infinite recursion when using where_exists() on a soft-deletable model.
    • Fixed some cases where a reconnection would not occur for PostgreSQL.
    Source code(tar.gz)
    Source code(zip)
  • 0.9.8(Oct 10, 2018)

    Fixed

    • Fixed the morphed_by_many() decorator.
    • Fixed decoding errors for MySQL.
    • Fixed connection errors check.
    • Fixed the touches() method.
    • Fixed has_many not showing DISTINCT.
    • Fixed save_many() for Python 3.
    • Fixed an error when listing columns for recent MySQL versions.
    Source code(tar.gz)
    Source code(zip)
  • 0.9.7(May 17, 2017)

  • 0.9.6(May 16, 2017)

    Added

    • Added support for DATE types in models.
    • Added support for fractional seconds for the TIMESTAMP type in MySQL 5.6.4+.
    • Added support for fractional seconds for the TIMESTAMP and TIME types in PostreSQL.

    Changed

    • Improved implementation of the chunk method.

    Fixed

    • Fixed timezone offset errors when inserting datetime aware objects into PostgreSQL.
    • Fixed a bug occurring when using __touches__ with an optional relationship.
    • Fixed collections serialization when using the query builder
    Source code(tar.gz)
    Source code(zip)
  • 0.9.5(Feb 12, 2017)

  • 0.9.4(Jan 12, 2017)

  • 0.9.3(Nov 10, 2016)

  • 0.9.2(Oct 18, 2016)

  • 0.9.1(Sep 29, 2016)

    Changed

    • Improves migrate command output when pretending.

    Fixed

    • Fixes errors when using PyMySQL.
    • Fixes use_qmark information not being passed to schema grammars.
    Source code(tar.gz)
    Source code(zip)
  • 0.9.0(Sep 15, 2016)

    Changed

    ORM
    Connection
    • Improved connectors.
    Schema
    • Makes the use_current=True the default for timestamps().
    Query
    • Allow usage of qmark syntax for all backends.
    • Made QueryBuilder return Collections.
    • Merging queries also merges columns.
    • Made query builder results accessible by attributes.
    DBAL
    • Improved connectors and dbal to detect platform versions.
    Collections
    • Removed Collection code and uses backpack package instead.

    Fixed

    ORM
    • Fixed the update of pivots.
    • Fixed behavior for dates accessor.
    • Fixed connection not being properly set when specifying the connection with on()
    Commands
    • Made the -P/--pretend command option work.
    Schema
    • Fixed schema grammars.
    • Fixed an error when modify a table with an enum column in MySQL.
    DBAL
    • Fixed behavior for enum columns.
    Source code(tar.gz)
    Source code(zip)
  • 0.8.2(Jul 25, 2016)

    Fixes

    ORM

    • Fixing a possible Memory Error: stack overflow error when accessing relations.
    • Fixing builder copying process to avoir issues with PyMySQL(thanks to ihumanable).

    Commands

    • Fixing the -n/--no-interaction option not automatically confirming questions.
    • Removing the check character in migration commands output to avoid errors on Windows.

    Connection

    • Updating connectors to raise an exception when backend packages are missing.
    • Adding standard name resolution to the purge method (thanks to ihumanable).

    DBAL

    • Fixing setting foreign key constraint name for MySQL.
    • Handling missing constraint_name for sqlite (thanks to ihumanable).
    Source code(tar.gz)
    Source code(zip)
  • 0.8.1(Jul 25, 2016)

    Fixes

    ORM

    • Removing call to Model._boot_columns() to avoid errors for column types not supported by the dbal.

    Schema Builder

    • Fixing Blueprint.char() method (thanks to ihumanable).
    • Fixing Fluent behavior.

    Commands

    • Fixing orator command not working on Windows.
    • Fixing migrate:status command not switching databases.

    Connection

    • Fixing a bug when calling Connnection.disconnect() after a reconnection when not using read/write connections.
    • Fixing MySQLConnection.get_server_version() method to be compatible with PyMySQL (thanks to gdraynz).
    Source code(tar.gz)
    Source code(zip)
  • 0.8.0(Jul 25, 2016)

    Improvements

    ORM

    • #30 Support for default values
    • #29 Supporting only one timetamp column on models
    • #26 Adding support for extra conditions on relationships
    • Adding @scope decorator to define query scopes.
    • Improving global scopes

    Schema builder

    • Adding support for a use_current() on timestamps
    • Improving dbal to support SQLite fully.
    • Improving fluents

    Query Builder

    • #28 Making where_in() method accept Collection instances

    Commands

    • Adding a make:model command

    Connection

    • Using unicode by default for mysql and postgres.
    • Improves how queries are run in Connection class

    Collections

    • Adds flatten() method to Collection class

    Fixes

    ORM

    • Fixes Model.get_foreign_key() method
    • Fixes soft deletes
    • Avoid going through setattr method when setting timestamps

    Schema Builder

    • #33 [SQLite] Renaming or dropping columns loses NULL constraint
    • #32 [SQLite] Renaming or dropping columns fails when columns' name is a keyword
    • #31 [SQLite] Changing columns loses default column values.

    Query Builder

    • Fixes query grammar default columns value

    Connection

    • Fixing Connection._try_again_if_caused_by_lost_connection() not being called
    • Preventing default connection being set to None
    • Fixing json type behavior for Postgres

    Migrations

    • Fixing migration stubs
    Source code(tar.gz)
    Source code(zip)
Owner
Sébastien Eustace
Software engineer, proud pythonista, open source lover. Creator of the Poetry package manager and the datetime library Pendulum.
Sébastien Eustace
A pure Python Database Abstraction Layer

pyDAL pyDAL is a pure Python Database Abstraction Layer. It dynamically generates the SQL/noSQL in realtime using the specified dialect for the databa

440 Nov 13, 2022
Solrorm : A sort-of solr ORM for python

solrorm : A sort-of solr ORM for python solrpy - deprecated solrorm - currently in dev Usage Cores The first step to interact with solr using solrorm

Aj 1 Nov 21, 2021
The ormar package is an async mini ORM for Python, with support for Postgres, MySQL, and SQLite.

python async mini orm with fastapi in mind and pydantic validation

1.2k Jan 05, 2023
The Orator ORM provides a simple yet beautiful ActiveRecord implementation.

Orator The Orator ORM provides a simple yet beautiful ActiveRecord implementation. It is inspired by the database part of the Laravel framework, but l

Sébastien Eustace 1.4k Jan 01, 2023
Redis OM Python makes it easy to model Redis data in your Python applications.

Object mapping, and more, for Redis and Python Redis OM Python makes it easy to model Redis data in your Python applications. Redis OM Python | Redis

Redis 568 Jan 02, 2023
A pythonic interface to Amazon's DynamoDB

PynamoDB A Pythonic interface for Amazon's DynamoDB. DynamoDB is a great NoSQL service provided by Amazon, but the API is verbose. PynamoDB presents y

2.1k Dec 30, 2022
Global base classes for Pyramid SQLAlchemy applications.

pyramid_basemodel pyramid_basemodel is a thin, low level package that provides an SQLAlchemy declarative Base and a thread local scoped Session that c

Grzegorz Śliwiński 15 Jan 03, 2023
ORM for Python for PostgreSQL.

New generation (or genius) ORM for Python for PostgreSQL. Fully-typed for any query with Pydantic and auto-model generation, compatible with any sync or async driver

Yan Kurbatov 3 Apr 13, 2022
Object mapper for Amazon's DynamoDB

Flywheel Build: Documentation: http://flywheel.readthedocs.org/ Downloads: http://pypi.python.org/pypi/flywheel Source: https://github.com/stevearc/fl

Steven Arcangeli 128 Dec 31, 2022
A PostgreSQL or SQLite orm for Python

Prom An opinionated lightweight orm for PostgreSQL or SQLite. Prom has been used in both single threaded and multi-threaded environments, including en

Jay Marcyes 18 Dec 01, 2022
The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy The Python SQL Toolkit and Object Relational Mapper Introduction SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that giv

mike bayer 3.5k Dec 29, 2022
Twisted wrapper for asynchronous PostgreSQL connections

This is txpostgres is a library for accessing a PostgreSQL database from the Twisted framework. It builds upon asynchronous features of the Psycopg da

Jan Urbański 104 Apr 22, 2022
Tortoise ORM is an easy-to-use asyncio ORM inspired by Django.

Tortoise ORM was build with relations in mind and admiration for the excellent and popular Django ORM. It's engraved in it's design that you are working not with just tables, you work with relational

Tortoise 3.3k Jan 07, 2023
Prisma Client Python is an auto-generated and fully type-safe database client

Prisma Client Python is an unofficial implementation of Prisma which is a next-generation ORM that comes bundled with tools, such as Prisma Migrate, which make working with databases as easy as possi

Robert Craigie 930 Jan 08, 2023
a small, expressive orm -- supports postgresql, mysql and sqlite

peewee Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use. a small, expressive ORM p

Charles Leifer 9.7k Jan 08, 2023
Python 3.6+ Asyncio PostgreSQL query builder and model

windyquery - A non-blocking Python PostgreSQL query builder Windyquery is a non-blocking PostgreSQL query builder with Asyncio. Installation $ pip ins

67 Sep 01, 2022
MongoEngine flask extension with WTF model forms support

Flask-MongoEngine Info: MongoEngine for Flask web applications. Repository: https://github.com/MongoEngine/flask-mongoengine About Flask-MongoEngine i

MongoEngine 815 Jan 03, 2023
Sqlalchemy-databricks - SQLAlchemy dialect for Databricks

sqlalchemy-databricks A SQLAlchemy Dialect for Databricks using the officially s

Flynn 19 Nov 03, 2022
Python helpers for using SQLAlchemy with Tornado.

tornado-sqlalchemy Python helpers for using SQLAlchemy with Tornado. Installation $ pip install tornado-sqlalchemy In case you prefer installing from

Siddhant Goel 122 Aug 23, 2022
A very simple CRUD class for SQLModel! ✨

Base SQLModel A very simple CRUD class for SQLModel! ✨ Inspired on: Full Stack FastAPI and PostgreSQL - Base Project Generator FastAPI Microservices I

Marcelo Trylesinski 40 Dec 14, 2022