Various utility functions, new data types and helpers for SQLAlchemy.
kvesteri / sqlalchemy-utils Goto Github PK
View Code? Open in Web Editor NEWVarious utility functions and datatypes for SQLAlchemy.
License: Other
Various utility functions and datatypes for SQLAlchemy.
License: Other
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).
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.
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:
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).
This would enable for example ordering of batch fetched relationships.
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)
The following exception is raised: "AttributeError: 'AliasedInsp' object has no attribute 'table'".
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
On most backends this could be saved as varchar.
Maybe on python side we could use:
http://docs.python.org/2/library/urlparse.html
@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?
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()
This could use babel for the implementation.
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.
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:
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.
NumberRange(-float('inf'), 10) works when adding or subtracting other number ranges but fails when trying to submit or read from database.
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 have no idea of the actual implementation yet. But this could definately use: http://babel.edgewall.org/
>>> 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
Similar to SQLAlchemy's joinedload and subqueryload, the batch_fetch should allow dot-separated deep relations eg.
batch_fetch(categories, 'articles.author')
The primitive type class should probably deserve its own package. We can copy a lot of stuff from Django SSN form fields.
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:
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.
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
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)
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.
We should be able to do:
session.query(User).filter(User.password == 'secret1234').all()
Edit: I added a failing test case for this.
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.
The following exception is raised: "AttributeError: 'AliasedInsp' object has no attribute 'table'".
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.
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
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
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
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
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()
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
I'm thinking of the following primary features:
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
.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)
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 ?
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.
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.