https://gist.github.com/nickretallack/8985745

Before committing to the database I have one time. When I retrieve it, I have a different time. The time that actually shows up in the database is the same as the time that is retrieved, not the time that is set, so the problem must occur when saving the time. The discrepancy is the same as the difference between my time zone and UTC, so it must be a time-zone-related issue.

Similar to SQLAlchemy's joinedload and subqueryload, the batch_fetch should allow dot-separated deep relations eg.

batch_fetch(categories, 'articles.author')

On most backends this could be saved as varchar.

Maybe on python side we could use:
http://docs.python.org/2/library/urlparse.html

>>> import sqlalchemy as sa
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.ext.hybrid import hybrid_property
>>> from sqlalchemy_utils import sort_query
>>>
>>> engine = create_engine('sqlite:///')
>>> Base = declarative_base()
>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>>
>>> class User(Base):
...    __tablename__ = 'user'
...    id = sa.Column(sa.Integer, primary_key=True)
...    first_name = sa.Column(sa.Unicode(255))
...    last_name = sa.Column(sa.Unicode(255))
...
...    @hybrid_property
...    def full_name(self):
...        return self.first_name + ' ' + self.last_name
...
>>> query = sort_query(session.query(User), '-full_name')
>>> print query
SELECT "user".id AS user_id, "user".first_name AS user_first_name, "user".last_name AS user_last_name
FROM "user" ORDER BY "user".first_name || :first_name_1 || "user".last_name

Note that the query above is missing the DESC keyword.

SQLAlchemy's query syntax works fine:

>>> print session.query(User).order_by(sa.desc(User.full_name))
SELECT "user".id AS user_id, "user".first_name AS user_first_name, "user".last_name AS user_last_name
FROM "user" ORDER BY ("user".first_name || :first_name_1 || "user".last_name) DESC

This could preferably be a subclass of FileType. When saving an ImageType to database there should be an option to populate separate height and width fields.

I'm not sure about the actual API yet, but something like this:

class Attachment(Base):
   image = Column(
       ImageType(width_field=image_width, height_field=image_height)
   )
   image_width = Column(sa.Integer)
   image_height = Column(sa.Integer)

We should be able to do:

session.query(User).filter(User.password == 'secret1234').all()

Edit: I added a failing test case for this.

Sometimes neither of the SQLAlchemy relationship fetching strategies (joinedload / lazyload and subqueryload) perform well enough. This is usually when using joined filters along with aggregated values and limit / offset. In these cases batch fetching the related data is the way to go. Hibernate has it. SQLAlchemy needs it. Let's do it :)

Some references:

https://groups.google.com/forum/#!topic/sqlalchemy/vHQlm0U5f2k

As Mike states there the implementation is hard for relations using custom joins and composite primary keys. For the first version we only need to support just some of the most basic cases.

users = session.query(User).all()

batch_fetch(users, User.addresses) # loads all addresses for all given users

Hi, I was trying to use PasswordType on a column, and on db.create_all() I got an error

ProgrammingError: (ProgrammingError) type "varbinary" does not exist
LINE 5:  password VARBINARY(1137) NOT NULL, 
                  ^
 '\nCREATE TABLE admin (\n\tid SERIAL NOT NULL, \n\tusername VARCHAR(64), \n\tpassword VARBINARY(1137) NOT NULL, \n\tPRIMARY KEY (id), \n\tUNIQUE (username)\n)\n\n' {}

Likely because PostgreSQL doesn't have(?) the type VARBINARY but BYTEA instead ( http://stackoverflow.com/questions/8057484/can-i-treat-varbinary-and-bytea-fields-equivalently ).

I'm using Flask-SQLAlchemy.

Is there a place for code like the following:

def sqlite3_regexp(pattern, text):
    return bool(re.search(pattern, text))

def on_connect(connection, record):
    connection.create_function('regexp', 2, sqlite3_regexp)
    connection.execute('PRAGMA foreign_keys=ON')

sa.event.listen(engine, 'connect', on_connect)

Somewhere for an easy:

from sqlalchemy_utils import connection_listener

sa.event.listen(engine, 'connect', connection_listener)

This should be file backend agnostic as much as possible. Preferably this type should use similar API to Django FileType (maybe some things could be done better though).

The primitive type class should probably deserve its own package. We can copy a lot of stuff from Django SSN form fields.

The following exception is raised: "AttributeError: 'AliasedInsp' object has no attribute 'table'".

NumberRange(-float('inf'), 10) works when adding or subtracting other number ranges but fails when trying to submit or read from database.

I'm thinking of the following primary features:

• A type decorator that maps a python uuid.UUID to a UUID type in postgres, mysql / mariadb (new versions), etc. and to a BINARY(16) in other dialects. This could also use the coercion listener to auto-convert strings, etc. to uuid.UUID.
• A column that wraps sa.Column and adds various arguments that map to arguments for uuid.UUID and an auto=True|False flag that will make using as a primary_key easier.

import uuid
import sqlalchemy as sa
from sqlalchemy_utils.columns import UUIDColumn
from sqlalchemy_utils.types import UUIDType

class ModelWithColumn(Base):
    id = UUIDColumn(version=4, primary_key=True, auto=True)

class ModelWithType(Base):
    id = sa.Column(
        UUIDType, 
        primary_key=True, 
        default=uuid.uuid4)

I have a number of nifty things I've developed (or found and spruced up) for sqlalchemy that don't have a real place at the moment and I'd like to find them one in open source land. What is the scope of this project? A grab-bag of all utils for sqlalchemy?

Some things for consideration:

• auto created / updated time columns
• uuid column
• timezone column
• data-based fixtures (nearly identical to django's with a couple of environmental changes: https://docs.djangoproject.com/en/dev/howto/initial-data/)
• django style object manager (https://docs.djangoproject.com/en/dev/topics/db/managers/)
• multi-db routing (think flask-alchemy binds plus django for_read / for_write / etc. -- declarative config for master/slave)
• declarative permissions using binary expressions

That's all I can think of.. I can probably dig up some more. The point is that I use a lot of these in a lot of our projects and It'd be nice to put them somewhere on github. If you don't think just adding some more buckets to this project would be a good idea; do you have another? We could always add lots of "extra_requires" (for any additional dependencies).

I'm using UUIDType on postgres but it doesn't seem to notice this and is attempting to insert binary data instead. This doesn't work.

The problem is here. It compares the dialect to a string, but the dialect isn't a string. It's <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10e84f110>. It should be looking at dialect.name like it does up here.

The following exception is raised: "AttributeError: 'AliasedInsp' object has no attribute 'table'".

The idea is to allow attaching arbitrary methods on the query API for sqlalchemy.

>>> u = session.query(User).filter_by_email(
...     '[email protected]').all()

The user-facing API for this could be as follows:

class User(Base):

    @sqlalchemy.managermethod
    def filter_by_email(cls, query, email):
        return query.filter(# [ ... ]

Or perhaps attach an object post-mortem of the model. Or a __manager__ property on the model.

Furthermore, this goes hand-in-hand with scoped sessions to allow the following (django-like) constructs:

>>> User.objects.filter_by_email('[email protected]').all()
>>> user = User.objects.create_superuser('bob', password='b')
>>> session.commit()

Something along the lines of:

class Article(Base):
   title = sa.Column(sa.Unicode(255))
   slug = sa.Column(SlugType(generator='title'))

Usage:

article = Article()
article.title = 'Cat was grumpy'
session.commit()
article.slug  # cat-was-grumpy

The generator should also allow callables:

class Article(Base):
   title = sa.Column(sa.Unicode(255))
   slug = sa.Column(SlugType(generator=lambda obj: (obj.id, obj.title)))

Callables that return tuples or lists would get hyphenated as follows:

article = Article()
article.title = 'Cat was grumpy'
session.commit()
article.slug  # 1-cat-was-grumpy

hi! I was wondering if I could propose some features using a fork. I have a lot of sqlalchemy utils under the belt, including some tweaks for some of your types.

best regards,
richard.

The query joins to two different classes (Team and League) that use the same base class (Entity) and joined table Inheritance. sort_query doesn't sort the query.

Running the tests with SQLAlchemy 0.9.0 results in a bunch errors like these:

../../../../.virtualenvs/sqlalchemy-utils/lib/python2.7/site-packages/py/_path/local.py:608: in pyimport
>               __import__(pkgpath.basename)
tests/__init__.py:9: in <module>
>   from sqlalchemy_utils import (
sqlalchemy_utils/__init__.py:1: in <module>
>   from .aggregates import aggregated
sqlalchemy_utils/aggregates.py:257: in <module>
>   from sqlalchemy.sql.expression import _FunctionGenerator
E   ImportError: cannot import name _FunctionGenerator

psycopg2 is stated as an install requirement in requirements.txt and setup.py

Is it really needed ?

Cant install sqlalchemy-utils via pip on non-postgresql systems.

Simple composite type wrapper around integer amount column and CurrencyType currency column.

http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#composite-column-types

https://github.com/poswald/python-money

Edit:

Python needs better money handling, something along the lines of:

https://github.com/RubyMoney/money

Thinking about something that stores Olson timezones (eg. America/Los_Angeles) using pytz. Auto-coercion from string form to the tzinfo objects from pytz.

Something like the below could be used so we don't depend on pytz.

try:
    import pytz

except ImportError:
    pytz = None

def __init__(self):
    if pytz is None:
        raise SomeError('pytz not available')

I've seen numerous factory-based and class-based fixtures. I'm thinking of something along the lines of a JSON / YAML / etc. file that stores the table data and can be loaded and dumped using a simple python API.

Close to django's implementation: https://docs.djangoproject.com/en/dev/howto/initial-data/

Maybe something similar to: http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#marshal-json-strings

On postgresql >9.2 this should use JSON type: http://www.postgresql.org/docs/9.2/static/datatype-json.html

On postgres this should use INET type. On other databases we need to check which drivers have native support for this. On the ones that don't have native support we can just use varchar.

Preferably this could use:

https://github.com/poswald/python-money

Edit: actually this could use https://github.com/limist/py-moneyed

However atm both python-money and py-moneyed are not using Babel. Rather they are both reinventing the wheel for currency formatting etc. I added a ticket for this: py-moneyed/py-moneyed#22

@mehcode I think it would be great if we could pass the allowed types to generic relationship. For example:

class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)

class Customer(Base):
    __tablename__ = 'customer'
    id = sa.Column(sa.Integer, primary_key=True)

class Event(Base):
    __tablename__ = 'event'
    id = sa.Column(sa.Integer, primary_key=True)
    object_type = sa.Column(sa.Unicode(255))
    object_id = sa.Column(sa.Integer)

    object = generic_relationship(object_type, object_id, [User, Customer])

This way we could control which classes the generic relationship accepts. This would also lead us to a path where we could add join expression support for generic relationships.

Consider the previous example. Now consider the following query:

session.query(Event).options(sa.orm.joinedload(Event.object))

This should fetch all events joined with customers and users.

What do you think?

It doesn't seem possible to use polymorphic_identity with a ChoiceType:

 class User(Base):
         Admin = (u'admin', 'Admin')
         Tenant = (u'tenant', 'Tenant')
         Landlord = (u'landlord', 'Landlord')

        TYPES = (Admin, Tenant, Landlord)
         type = Column(ChoiceType(TYPES), nullable=False)
         # other fields....
        __mapper_args__ = {
    'polymorphic_on': type,
       }

 class Tenant(User):
       __mapper_args__ = {'polymorphic_identity': u'tenant'}

  t = Tenant(first_name='xxxx', last_name='xxxx', email='[email protected]')
  DBSession.add(t)
  transaction.commit()             

Result:
File "local/lib/python2.7/site-packages/SQLAlchemy_Utils-0.20.0-py2.7.egg/sqlalchemy_utils/types/choice.py", line 107, in process_bind_param
return value.code
sqlalchemy.exc.StatementError: 'unicode' object has no attribute 'code' (original cause: AttributeError: 'unicode' object has no attribute 'code') u'INSERT INTO users (type, first_name, last_name, email) VALUES (?, ?, ?, ?)' [{'first_name': 'xxxx', 'last_name': 'xxxx', 'type': u'tenant', 'email': '[email protected]'}]

I tried explicitly passing in the Choice value:

 t = Tenant(type=Choice('tenant', 'Tenant'))

This "worked" as I was able to save the instance, however when trying to get the instance:
t = DBSession.query(Tenant).first()

/local/lib/python2.7/site-packages/SQLAlchemy-0.8.4-py2.7-linux-i686.egg/sqlalchemy/orm/loading.pyc in configure_subclass_mapper(discriminator)
531 raise AssertionError(
532 "No such polymorphic_identity %r is defined" %
--> 533 discriminator)
534 if sub_mapper is mapper:
535 return None

AssertionError: No such polymorphic_identity Choice(code=tenant, value=Tenant) is defined

A password that wraps usage of passlib and stores the password as a BINARY hash.

class User(Base):
    password = PasswordColumn(context={
        # config from passlib
    })

See http://pythonhosted.org/passlib/lib/passlib.context-tutorial.html#basic-usage for some config usage of the context.

For using the password column, perhaps something like this:

>>> u = User()
>>> u.password = User.password.hash('b')
>>> User.password.check(u.password, 'b')
True

Or if we don't care about allowing direct set of the hash (probably preferred API).

>>> u = User()
>>> u.password = 'b'
>>> u.password
'$5$rounds=80000$H.............'

>>> u.password == 'b'
True

Some cool things passlib does:

• Easily support authentication against nearly every kind of password scheme (even django's custom stuff)
• Auto-update stored hash to preferred format when checking password

Just to note. I have most of this stuff written. Let's agree on the API and I'll formalize a PR with tests, etc.

Expected result: relationship is cleared "set to NULL in the db", the object property should be None when accessed in subsequent calls.

Exception:
  File "user.py", line 306, in add_activity
    activity.object = None
  File "/sqlalchemy/orm/attributes.py", line 220, in __set__
    instance_dict(instance), value, None)
  File "/sqlalchemy_utils/generic.py", line 55, in set
    mapper = class_mapper(type(initiator))
  File "/sqlalchemy/orm/base.py", line 379, in class_mapper
    raise exc.UnmappedClassError(class_)
UnmappedClassError: Class '__builtin__.NoneType' is not mapped

Committing a PasswordType that has been updated by the verify_and_update method because it's using a deprecated scheme doesn't work correctly for me.

After comparing the password with the correct one, the value is actually updated with the new scheme but when I commit my object, the change isn't reflected to the DB.

Here's a minimal code to reproduce the behavior (using Python 3.3.2):

from sqlalchemy import create_engine, Column, Integer, event
from sqlalchemy.orm import sessionmaker, mapper
from sqlalchemy.ext.declarative import declarative_base
from passlib.hash import md5_crypt
from sqlalchemy_utils import Password, PasswordType, coercion_listener

sql_engine = create_engine('sqlite:///testdb.sqlite')
Session = sessionmaker(bind=sql_engine)
session = Session()
Base = declarative_base()
event.listen(mapper, 'mapper_configured', coercion_listener)


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

    password = Column(PasswordType(
        schemes=[
            'pbkdf2_sha512',
            'md5_crypt'
        ],

        deprecated=['md5_crypt']
    ))

Base.metadata.drop_all(sql_engine)
Base.metadata.create_all(sql_engine)

user = User()
session.add(user)

user.password = Password(md5_crypt.encrypt('b').encode('utf-8'))
session.commit()

print(user.password.hash)           # The md5 hash
print(user.password == 'b')         # True and update the hash
print(user.password.hash)           # The hash is now pbkdf2_sha512
session.commit()                    # Nothing changes

user = session.query(User).get(1)   # Get the previous object from the database
print(user.password.hash)           # The password is still a md5 hash instead of pbkdf2_sha512

Is this the correct behavior or am I missing something ?

http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.column_descriptions#sqlalchemy.orm.query.Query.column_descriptions

I have no idea of the actual implementation yet. But this could definately use: http://babel.edgewall.org/

Trying to change JSONType fields results in SQLAlchemy complaining with TypeError: unbound method compare_values() must be called with UnicodeText instance as first argument (got dict instance instead)

Test code:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy_utils import JSONType

config = {
    'SQLALCHEMY_DATABASE_URI': 'sqlite://',
    'DEBUG': True,
}

app = Flask('jsontest')
app.config.update(config)
db = SQLAlchemy(app)

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    properties = db.Column(JSONType)

@app.route('/create')
def create():
    person = Person()
    person.properties = {'name': 'Gregor Samsa'}
    db.session.add(person)
    db.session.commit()
    return str(person.properties)

@app.route('/get')
def get():
    return str(Person.query.first().properties)

@app.route('/edit')
def edit():
    person = Person.query.first()
    person.properties = {'name': 'Kafka'}
    db.session.commit()
    return str(person.properties)


if __name__ == '__main__':
    with app.test_request_context():
        db.create_all()
    client = app.test_client()
    print client.get('/create').data
    print client.get('/get').data
    print client.get('/edit').data

/create and /get works just like expected, but the final call to /edit crashes.

user = session.query(User).get(1)
user.password.hash = md5_crypt.encrypt('b')
session.commit()

Similar problem as #49. Assigning to .hash directly should either be disallowed or should work and call .changed(). Don't have time to fix this right now (this bug report is to remind me to fix it later) but (as a work around) this should work:

user = session.query(User).get(1)
user.password.hash = md5_crypt.encrypt('b')
user.password.changed()
session.commit()

https://github.com/crsmithdev/arrow

This could use babel for the implementation.

This would enable for example ordering of batch fetched relationships.