[sqlalchemy] Re: query with one attribute of table to list

2012-04-12 Thread lestat 
Maybe in sqlalchemy add attribute like "flat=True" that return list with 
values (instead of tuples with values) ?


вторник, 27 марта 2012 г., 19:27:04 UTC+4 пользователь lestat написал:
>
> If I want get list of id's of table I can get it with
>
> z = db.session.query(MyTable.id).all()
>
> but it return list like [(7), (13)],
> but I want like [7, 13].
>
> Now I do it with [x[0] for x in z]
>
> Maybe is exists better solution?
>
> Thanks
>
>
>
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/mrvUmqkWpbwJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] DuplicateKeyError exception

2012-03-30 Thread lestat 


> if you want to rethrow DBAPI exceptions you'd need to catch it at some 
> point.   We don't have a hook where you can build in a "try:/except:" 
> around executions right now so such a thing would need to be called 
> externally to SQLAlchemy:
>


Ok, thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/XuN0PVoVMFUJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] DuplicateKeyError exception

2012-03-30 Thread lestat 
Hi!

I use postgresql and I try create my exception DuplicateKeyError 
inheritanced from IntegrityError.

I know that duplicate key in postgresql has code 23505 and I must catch with
if err.orig.pgcode == '23505':
...

How I can create this?

I don't work with many exceptions, and don't find what method it execute 
when it raised.

I try:

from sqlalchemy.exc import IntegrityError


class DuplicateKeyError(IntegrityError):
def __init__(self, *args, **kwargs):
print '__DuplicateKeyError init called'
print ' args:',args
print ' kwargs:',kwargs
super(DuplicateKeyError, self).__init__(*args, **kwargs)

@classmethod
def instance(cls, *args, **kwargs):
print '__DuplicateKeyError instance called'
print ' cls:',cls
print ' args:',args
print ' kwargs:',kwargs
super(DuplicateKeyError, cls).instance(*args, **kwargs)

@staticmethod
def __new__(cls, *args, **kwargs):
print '__DuplicateKeyError new called'
print ' cls:',cls
print ' args:',args
print ' kwargs:',kwargs
super(DuplicateKeyError, cls).__new__(*args, **kwargs)

def __call__(self, *args, **kwargs):
print '__DuplicateKeyError call called'
print ' self:',self
print ' args:',args
print ' kwargs:',kwargs
super(DuplicateKeyError, self).__call__(*args, **kwargs)

def test():
try:
i = Interest(name='x1')
db.session.add(i)
db.session.commit()
except DuplicateKeyError as err:
print '= err:',err
print '= err.orig.pgcode:',err.orig.pgcode


but nothing of my prints does not executed.

Where I wrong?

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/ExQsD7uUi6MJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] query with one attribute of table to list

2012-03-27 Thread lestat 
If I want get list of id's of table I can get it with

z = db.session.query(MyTable.id).all()

but it return list like [(7), (13)],
but I want like [7, 13].

Now I do it with [x[0] for x in z]

Maybe is exists better solution?

Thanks




-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/At4WmW6_26AJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] NEW and OLD objects in sqlalchemy events

2012-01-31 Thread lestat 
I try rewrite trigger for postgresql in python sqlalchemy event
function.

Can I in sqlalchemy event context get NEW and OLD objects?

I need compare attributes of NEW and OLD objects.


Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] duplicate key trick

2012-01-20 Thread lestat 
Hi!

Maybe anyone can tell how I can except this error?


class TmpTest(db.Model, UnicodeMixin):
__tablename__ = 'tmp_test'

id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False, unique=True)
count = db.Column(db.Integer, nullable=False, default=0,
server_default='0')
time_create = db.Column(db.DateTime, nullable=False,
default=func.now(), server_default=expression.text('now()'))




from models import TmpTest
from sqlalchemy.orm.exc import NoResultFound
from app import db
from multiprocessing import Pool, Process

names = ['tmp_test_{0}'.format(num) for num in range(1000)]

def sqlalchemy_test():
for name in names:
try:
t = TmpTest.query.filter_by(name=name).one()
except NoResultFound:
t = TmpTest()
t.name = name
t.count = 0
t.count += 1
t = db.session.merge(t)
db.session.add(t)
db.session.commit()


if __name__ == '__main__':
p1 = Process(target=sqlalchemy_test)
p1.daemon = True
p2 = Process(target=sqlalchemy_test)
p2.daemon = True
p3 = Process(target=sqlalchemy_test)
p3.daemon = True
p4 = Process(target=sqlalchemy_test)
p4.daemon = True
p5 = Process(target=sqlalchemy_test)
p5.daemon = True
p6 = Process(target=sqlalchemy_test)
p6.daemon = True
p7 = Process(target=sqlalchemy_test)
p7.daemon = True
p8 = Process(target=sqlalchemy_test)
p8.daemon = True
p9 = Process(target=sqlalchemy_test)
p9.daemon = True
p10 = Process(target=sqlalchemy_test)
p10.daemon = True
p1.start()
p2.start()
p3.start()
p4.start()
p5.start()
p6.start()
p7.start()
p8.start()
p9.start()
p10.start()
p1.join()
p2.join()
p3.join()
p4.join()
p5.join()
p6.join()
p7.join()
p8.join()
p9.join()
p10.join()



IntegrityError: (IntegrityError) duplicate key value violates unique
constraint "tmp_test_name_key"
DETAIL:  Key (name)=(tmp_test_45) already exists.
 'INSERT INTO tmp_test (name, count, time_create) VALUES (%(name)s, %
(count)s, now()) RETURNING tmp_test.id' {'count': 1, 'name':
'tmp_test_45'}


IntegrityError: (IntegrityError) duplicate key value violates unique
constraint "tmp_test_name_key"
DETAIL:  Key (name)=(tmp_test_26) already exists.
 'INSERT INTO tmp_test (name, count, time_create) VALUES (%(name)s, %
(count)s, now()) RETURNING tmp_test.id' {'count': 1, 'name':
'tmp_test_26'}
Process Process-7:


etc...

Thanks!


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] bug in DDL event with %

2012-01-18 Thread lestat 
Hi!


If I create DDL like this with %:

event.listen(db.metadata, 'after_create', DDL(
"""
CREATE OR REPLACE FUNCTION test_func() RETURNS void AS $$
DECLARE
max_user_id INTEGER;
BEGIN
SELECT INTO max_user_id MAX("id") FROM "user";
RAISE INFO 'max_user_id: (%)', max_user_id;
END;
$$ LANGUAGE plpgsql;

"""
))


and in python shell call db.create_all() for create tables I got
exception:



> 1 db.create_all()

/usr/lib/python2.7/site-packages/flaskext/sqlalchemy.pyc in
create_all(self, bind, app)
793Parameters were added
794 """
--> 795 self._execute_for_all_tables(app, bind, 'create_all')
796
797 def drop_all(self, bind='__all__', app=None):

/usr/lib/python2.7/site-packages/flaskext/sqlalchemy.pyc in
_execute_for_all_tables(self, app, bind, operation)
785 tables = self.get_tables_for_bind(bind)
786 op = getattr(self.Model.metadata, operation)
--> 787 op(bind=self.get_engine(app, bind), tables=tables)
788
789 def create_all(self, bind='__all__', app=None):

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in
create_all(self, bind, tables, checkfirst)
   2513 self,
   2514 checkfirst=checkfirst,
-> 2515 tables=tables)
   2516
   2517 def drop_all(self, bind=None, tables=None,
checkfirst=True):

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in
_run_visitor(self, visitorcallable, element, connection, **kwargs)
   2232 conn = connection
   2233 try:
-> 2234 conn._run_visitor(visitorcallable, element,
**kwargs)
   2235 finally:
   2236 if connection is None:

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in
_run_visitor(self, visitorcallable, element, **kwargs)
   1902 def _run_visitor(self, visitorcallable, element,
**kwargs):
   1903 visitorcallable(self.dialect, self,
-> 1904 **kwargs).traverse_single(element)
   1905
   1906

/usr/lib/python2.7/site-packages/sqlalchemy/sql/visitors.pyc in
traverse_single(self, obj, **kw)
 84 meth = getattr(v, "visit_%s" % obj.__visit_name__,
None)
 85 if meth:
---> 86 return meth(obj, **kw)
 87
 88 def iterate(self, obj):

/usr/lib/python2.7/site-packages/sqlalchemy/engine/ddl.pyc in
visit_metadata(self, metadata)
 70 tables=collection,
 71
checkfirst=self.checkfirst,
---> 72 _ddl_runner=self)
 73
 74 def visit_table(self, table, create_ok=False):

/usr/lib/python2.7/site-packages/sqlalchemy/event.pyc in
__call__(self, *args, **kw)
272 fn(*args, **kw)
273 for fn in self.listeners:
--> 274 fn(*args, **kw)
275
276 # I'm not entirely thrilled about the overhead here,


/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in
__call__(self, target, bind, **kw)
   2823
   2824 if self._should_execute(target, bind, **kw):
-> 2825 return bind.execute(self.against(target))
   2826
   2827 def _check_ddl_on(self, on):

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in
execute(self, object, *multiparams, **params)
   1403 object,
   1404 multiparams,
-> 1405 params)
   1406 else:
   1407 raise exc.InvalidRequestError(

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in
_execute_ddl(self, ddl, multiparams, params)
   1488 dialect = self.dialect
   1489
-> 1490 compiled = ddl.compile(dialect=dialect)
   1491 ret = self._execute_context(
   1492 dialect,

/usr/lib/python2.7/site-packages/sqlalchemy/sql/expression.pyc in
compile(self, bind, dialect, **kw)
   1720 else:
   1721 dialect = default.DefaultDialect()
-> 1722 return self._compiler(dialect, bind=bind, **kw)
   1723
   1724 def _compiler(self, dialect, **kw):

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in
_compiler(self, dialect, **kw)
   2850 Dialect."""
   2851
-> 2852 return dialect.ddl_compiler(dialect, self, **kw)
   2853
   2854 class DDL(DDLElement):

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in
__init__(self, dialect, statement, bind)
697 self.statement = statement
698 self.can_execute = statement.supports_execution
--> 699 self.string = self.process(self.statement)
700
701 @util.deprecated("0.7", ":class:`.Compiled` objects now
compile "

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in
process(self, obj, **kwargs)
716
717 def process(self, obj, **k

[sqlalchemy] Flask-SQLAlchemy and pgpool

2011-12-24 Thread lestat 
We using Flask-SQLAlchemy extension, and we try first using pgpool &
pgbouncer for postgresql cluster.

in settings:
SQLALCHEMY_POOL_SIZE = 50
SQLALCHEMY_POOL_TIMEOUT = 60

If we turn on pgpool we get errors:
ProcessFrontendResponse: failed to read kind from frontend. frontend
abnormally exited

Maybe we need forcibly close connection to postgresql after each
request?

If I understood sqlalchemy using their pool to postgresql and using
existied connections to postgresql and not reopen it before each
request?

Anyon encountered this problem?




Thanks!














-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] create unique constraint on lower field

2011-12-20 Thread lestat 
Can I set in sqlalchemy model unique=True, but with unique on lower of
that field?

Or it can be setted only like

event.listen(Tag.__table__, 'after_create', DDL(
"""
CREATE UNIQUE INDEX ON tag (LOWER(title))
"""
))

?

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] how can I set several tables for event?

2011-12-13 Thread lestat 
I create sql functions and triggers after creating tables in db.

e.g.:

event.listen(MyTestModel.__table__, 'after_create', DDL(
"""
CREATE OR REPLACE FUNCTION testfunc() RETURNS TRIGGER AS $$
...

CREATE TRIGGER 
"""
))


Can I set several tables for event, after creating them create my
triggers?
Or order for event for creating triggers?

Something like
event.listen(MyTestModel.__table__, MyTestModel2.__table__,
'after_create', DDL(
"""
CREATE OR REPLACE FUNCTION testfunc() RETURNS TRIGGER AS $$
...

CREATE TRIGGER 
"""
))

or
event.listen(MyTestModel.__table__, order={'test': 1}, 'after_create',
DDL(
"""
CREATE OR REPLACE FUNCTION testfunc() RETURNS TRIGGER AS $$
...

CREATE TRIGGER 
"""
))

event.listen(MyTestModel2.__table__, order={'test': 2},
'after_create', DDL(
"""
CREATE OR REPLACE FUNCTION testfunc() RETURNS TRIGGER AS $$
...

CREATE TRIGGER 
"""
))


Or creating functions and triggers after creating all tables in db.


Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] how add comment before query statement?

2011-12-12 Thread lestat 
For our postgresql cluster we need sometime append comment before query 
statement.

E.g.
q = Comment.query.all()

SELECT ... FROM comment

How append comment like this?
/*NO LOAD BALANCE*/ SELECT ... FROM comment

I try change q.statement, but can't find right solution.

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/WR0Oa-oZAx0J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] empty query

2011-12-11 Thread lestat 
/sqlalchemy/sql/expression.py:1869: SAWarning: The IN-predicate on
"comment.reply_id" was invoked with an empty sequence. This results in
a contradiction, which nonetheless can be expensive to evaluate.
Consider alternative strategies for improved performance.
  return self._in_impl(operators.in_op, operators.notin_op, other)

I try fix this warning.

user_comments_ids = tuple(x[0] for x in
getCommentsForUser(user_id).values('id'))
if user_comments_ids:
comments_replies =
Comment.query.filter(Comment.reply_id.in_(user_comments_ids))
else:
# empty query
comments_replies = Comment.query.filter_by(id=None)
return comments_replies

but exists better solution?

Why not add to sqlalchemy Query method like .all(), .one(),  new
method .none() like in django orm?


Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] sqlalchemy memory usage

2011-11-23 Thread lestat 
if I do

for x in xrange(1):
u = User.query.get(x)

that python process memory up to 70 mb, and after cycle I try do

db.session.close_all()

or

db.session.expire_all()

but process memory not clear and it still 70 mb.

If I working with millions of objects and try clearing memory, it use
all operating memory in system.

How I can solve this problem?

Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] exists for query

2011-11-18 Thread lestat 

I often pass db query in template context, and in template I check if
it exists, that render one html block, else other html block.

Can I in sqlalchemy check existance of query?

Now I simply call query.count() in template as temporary solution.

For example:
q = User.query.filter(User.email=='1...@1.ru')

maybe in future releases can add for q method exists and it improve
sqlalchemy usage.
in templates may call q.exist().

Now I see solution as check existance in view/controller and then pass
it variable to template context via
db.session.query(exists().where(User.email=='1...@1.ru')).scalar()
but it not very friendly.

Maybe you tell better solution?

Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] default NULL

2011-11-10 Thread lestat 
How I can add default NULL for column?

class UserIp(db.Model, UnicodeMixin):
__tablename__ = 'user_ip'

user_id = db.Column(db.Integer, db.ForeignKey('user.id'),
primary_key=True, nullable=True, server_default=None)
ip = db.Column(postgres.CIDR, nullable=False,
server_default='127.0.0.1/32')
time_last = db.Column(db.DateTime, nullable=True,
server_default=None)

I tried server_default=None, default=None, server_default='NULL', and
it not works.

I need that sqlalchemy generate create table with
"time_last" TIMESTAMP NULL DEFAULT NULL
instead
"time_last" TIMESTAMP NULL

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] UniqueConstraint with func

2011-11-10 Thread lestat 
Can I add UniqueConstraint like this?


from sqlalchemy import func
from sqlalchemy.schema import UniqueConstraint
from sqlalchemy.dialects import postgres

class UserIp(db.Model):
__tablename__ = 'user_ip'

__table_args__ = (
UniqueConstraint('user_id', func.md5('ip')),
)

user_id = db.Column(db.Integer, db.ForeignKey('user.id'),
primary_key=True, default=None)
ip = db.Column(postgres.CIDR, nullable=False,
default='127.0.0.1/32')



It raise exception:

/usr/lib/python2.7/site-packages/flaskext/sqlalchemy.pyc in
__init__(self, name, bases, d)
465 def __init__(self, name, bases, d):
466 bind_key = d.pop('__bind_key__', None)
--> 467 DeclarativeMeta.__init__(self, name, bases, d)
468 if bind_key is not None:
469 self.__table__.info['bind_key'] = bind_key

/usr/lib/python2.7/site-packages/sqlalchemy/ext/declarative.pyc in
__init__(cls, classname, bases, dict_)
   1257 return type.__init__(cls, classname, bases, dict_)
   1258 else:
-> 1259 _as_declarative(cls, classname, cls.__dict__)
   1260 return type.__init__(cls, classname, bases, dict_)
   1261

/usr/lib/python2.7/site-packages/sqlalchemy/ext/declarative.pyc in
_as_declarative(cls, classname, dict_)
   1156 cls.__table__ = table = Table(tablename,
cls.metadata,
   1157   *(tuple(cols) +
tuple(args)),
-> 1158**table_kw)
   1159 else:
   1160 table = cls.__table__

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in __new__(cls,
*args, **kw)
267 metadata._add_table(name, schema, table)
268 try:
--> 269 table._init(name, metadata, *args, **kw)
270 table.dispatch.after_parent_attach(table,
metadata)
271 return table

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in _init(self,
name, metadata, *args, **kwargs)
342 # initialize all the column, etc. objects.  done after
reflection to
343 # allow user-overrides
--> 344 self._init_items(*args)
345
346 @property

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in
_init_items(self, *args)
 62 for item in args:
 63 if item is not None:
---> 64 item._set_parent_with_dispatch(self)
 65
 66 def get_children(self, **kwargs):

/usr/lib/python2.7/site-packages/sqlalchemy/events.pyc in
_set_parent_with_dispatch(self, parent)
228 def _set_parent_with_dispatch(self, parent):
229 self.dispatch.before_parent_attach(self, parent)
--> 230 self._set_parent(parent)
231 self.dispatch.after_parent_attach(self, parent)
232

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in
_set_parent(self, table)
   1827
   1828 def _set_parent(self, table):
-> 1829 ColumnCollectionMixin._set_parent(self, table)
   1830 Constraint._set_parent(self, table)
   1831

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in
_set_parent(self, table)
   1801 if isinstance(col, basestring):
   1802 col = table.c[col]
-> 1803 self.columns.add(col)
   1804
   1805 class ColumnCollectionConstraint(ColumnCollectionMixin,
Constraint):

/usr/lib/python2.7/site-packages/sqlalchemy/sql/expression.pyc in
add(self, column)
   2135
   2136 """
-> 2137 self[column.key] = column
   2138
   2139 def __delitem__(self, key):

AttributeError: 'Function' object has no attribute 'key'




Thanks!


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: get raw sql for create type and tables

2011-10-28 Thread lestat 
If I do this:

from sqlalchemy import *
from StringIO import StringIO
buf = StringIO()
pg_engine = create_engine('sqlite://', strategy='mock',
executor=lambda s,p=';': buf.write(s.__str__() + p))
buf.truncate(0)
tables = [x[1] for x in sorted(db.metadata.tables.items(), key=lambda
x: x[0])]
for table in tables:
table.create(pg_engine)
print buf.getvalue()

ok, it prints,

but if I change engine to 'postgres://'
then sqlalchemy print error:

---
AttributeErrorTraceback (most recent call
last)


 59 tables = [x[1] for x in sorted(db.metadata.tables.items(),
key=lambda x: x[0])]
 60 for table in tables:
---> 61 table.create(pg_engine)
 62 print buf.getvalue()
 63

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in create(self,
bind, checkfirst)
525 bind._run_visitor(ddl.SchemaGenerator,
526 self,
--> 527 checkfirst=checkfirst)
528
529

/usr/lib/python2.7/site-packages/sqlalchemy/engine/strategies.pyc in
_run_visitor(self, visitorcallable, element, connection, **kwargs)
247 kwargs['checkfirst'] = False
248 visitorcallable(self.dialect, self,
--> 249 **kwargs).traverse(element)
250
251 def execute(self, object, *multiparams, **params):

/usr/lib/python2.7/site-packages/sqlalchemy/sql/visitors.pyc in
traverse(self, obj)
 94 """traverse and visit the given expression
structure."""
 95
---> 96 return traverse(obj, self.__traverse_options__,
self._visitor_dict)
 97
 98 @util.memoized_property

/usr/lib/python2.7/site-packages/sqlalchemy/sql/visitors.pyc in
traverse(obj, opts, visitors)
205 """traverse and visit the given expression structure using
the default iterator."""
206
--> 207 return traverse_using(iterate(obj, opts), obj, visitors)
208
209 def traverse_depthfirst(obj, opts, visitors):

/usr/lib/python2.7/site-packages/sqlalchemy/sql/visitors.pyc in
traverse_using(iterator, obj, visitors)
199 meth = visitors.get(target.__visit_name__, None)
200 if meth:
--> 201 meth(target)
202 return obj
203

/usr/lib/python2.7/site-packages/sqlalchemy/engine/ddl.pyc in
visit_table(self, table, create_ok)
 74
 75 table.dispatch.before_create(table, self.connection,
---> 76
checkfirst=self.checkfirst)
 77
 78 for column in table.columns:

/usr/lib/python2.7/site-packages/sqlalchemy/event.pyc in
__call__(self, *args, **kw)
272 fn(*args, **kw)
273 for fn in self.listeners:
--> 274 fn(*args, **kw)
275
276 # I'm not entirely thrilled about the overhead here,

/usr/lib/python2.7/site-packages/sqlalchemy/schema.pyc in
adapt_listener(target, connection, **kw)
484
485 def adapt_listener(target, connection, **kw):
--> 486 listener(event_name, target, connection)
487
488 event.listen(self, "" + event_name.replace('-', '_'),
adapt_listener)

/usr/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc in
__call__(self, *arg, **kw)
279
280 def __call__(self, *arg, **kw):
--> 281 return getattr(self.target, self.name)(*arg, **kw)
282
283 def class_hierarchy(cls):

/usr/lib/python2.7/site-packages/sqlalchemy/types.pyc in
_on_table_create(self, event, target, bind, **kw)
   1676 t = self.dialect_impl(bind.dialect)
   1677 if t.__class__ is not self.__class__ and isinstance(t,
SchemaType):
-> 1678 t._on_table_create(event, target, bind, **kw)
   1679
   1680 def _on_table_drop(self, event, target, bind, **kw):

/usr/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/
base.pyc in _on_table_create(self, event, target, bind, **kw)
451
452 def _on_table_create(self, event, target, bind, **kw):
--> 453 self.create(bind=bind, checkfirst=True)
454
455 def _on_metadata_create(self, event, target, bind, **kw):

/usr/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/
base.pyc in create(self, bind, checkfirst)
439
440 if not checkfirst or \
--> 441 not bind.dialect.has_type(bind, self.name,
schema=self.schema):
442 bind.execute(CreateEnumType(self))
443

/usr/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/
base.pyc in has_type(self, connection, type_name, schema)
   1037 """
   1038 cursor = connection.execute(sql.text(query,
bindparams=bindparams))
-> 1039 return bool(cursor.scalar())
   1040
   1041 def _get_server_version_info(self, connection):

AttributeError: 'NoneType' object has no attribute 'scalar'





-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this gro

[sqlalchemy] Re: get raw sql for create type and tables

2011-10-28 Thread lestat 
I need print this sql code if these ENUM types and tables and indexes
exist.




On 28 окт, 18:28, lestat  wrote:
> How I can get raw sql for this table?
>
> class Test(db.Model):
>     __tablename__ = 'test'
>
>     id = db.Column(db.Integer, primary_key=True)
>     birthday = db.Column(db.Date)
>     gender = db.Column(db.Enum('male', 'female', name='TYPE_GENDER'))
>     relationship_status = db.Column(db.Enum('free', 'in_relationship',
> 'married', 'complicated', 'in_open_relationship', 'widowed',
> name='TYPE_RELATION'))
>     relationship_user_id = db.Column(db.Integer,
> db.ForeignKey('test.id'))
>     relationship_user = db.relationship('Test',
> backref=db.backref('parent', remote_side=id))
>     religion_id = db.Column(db.Integer, db.ForeignKey('religion.id'))
>     religion = db.relationship('Religion', backref=db.backref('user'),
> cascade='all, delete, delete-orphan')
>
> I need get something like that:
> CREATE TYPE "TYPE_GENDER" AS ENUM ('male','female')
>
> CREATE TYPE "TYPE_RELATION" AS ENUM
> ('free','in_relationship','married','complicated','in_open_relationship','w 
> idowed')
>
> CREATE TABLE test (
>         id SERIAL NOT NULL,
>         birthday DATE,
>         gender "TYPE_GENDER",
>         relationship_status "TYPE_RELATION",
>         relationship_user_id INTEGER,
>         religion_id INTEGER,
>         PRIMARY KEY (id),
>         FOREIGN KEY(relationship_user_id) REFERENCES test (id),
>         FOREIGN KEY(religion_id) REFERENCES religion (id)
> )
>
> CREATE INDEX ix_test_relationship_user_id ON test
> (relationship_user_id)
>
> Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] get raw sql for create type and tables

2011-10-28 Thread lestat 
How I can get raw sql for this table?



class Test(db.Model):
__tablename__ = 'test'

id = db.Column(db.Integer, primary_key=True)
birthday = db.Column(db.Date)
gender = db.Column(db.Enum('male', 'female', name='TYPE_GENDER'))
relationship_status = db.Column(db.Enum('free', 'in_relationship',
'married', 'complicated', 'in_open_relationship', 'widowed',
name='TYPE_RELATION'))
relationship_user_id = db.Column(db.Integer,
db.ForeignKey('test.id'))
relationship_user = db.relationship('Test',
backref=db.backref('parent', remote_side=id))
religion_id = db.Column(db.Integer, db.ForeignKey('religion.id'))
religion = db.relationship('Religion', backref=db.backref('user'),
cascade='all, delete, delete-orphan')


I need get something like that:
CREATE TYPE "TYPE_GENDER" AS ENUM ('male','female')

CREATE TYPE "TYPE_RELATION" AS ENUM
('free','in_relationship','married','complicated','in_open_relationship','widowed')


CREATE TABLE test (
id SERIAL NOT NULL,
birthday DATE,
gender "TYPE_GENDER",
relationship_status "TYPE_RELATION",
relationship_user_id INTEGER,
religion_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(relationship_user_id) REFERENCES test (id),
FOREIGN KEY(religion_id) REFERENCES religion (id)
)


CREATE INDEX ix_test_relationship_user_id ON test
(relationship_user_id)



Thanks.


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] how to compile statement from string and params dict?

2011-10-21 Thread lestat 
How I can compile statement with this 2 objects:


st = """ SELECT "user".id AS user_id, "user".username AS
user_username, "user".email AS user_email, "user".password AS
user_password, "user".bd AS user_bd, "user".first_name AS
user_first_name, "user".last_name AS user_last_name,
"user".middle_name AS user_middle_name, "user".is_active_flag AS
user_is_active_flag, "user".time_create AS user_time_create,
"user".time_last_activity AS user_time_last_activity,
"user".subscribe_news AS user_subscribe_news, "user".is_deleted AS
user_is_deleted, "user".is_banned AS user_is_banned
FROM "user"
WHERE "user".id != "user".id AND "user".time_last_activity > %
(time_last_activity_1)s ORDER BY "user".time_last_activity DESC
 LIMIT %(param_1)s """

params = {'time_last_activity_1': datetime.datetime(2011, 10, 21, 17,
36, 21, 577026), 'param_1': 3}

If I do
result = st % params
then I get not valid sql:
' SELECT "user".id AS user_id, "user".username AS user_username,
"user".email AS user_email, "user".password AS user_password,
"user".bd AS user_bd, "user".first_name AS user_first_name,
"user".last_name AS user_last_name, "user".middle_name AS
user_middle_name, "user".is_active_flag AS user_is_active_flag,
"user".time_create AS user_time_create, "user".time_last_activity AS
user_time_last_activity, "user".subscribe_news AS user_subscribe_news,
"user".is_deleted AS user_is_deleted, "user".is_banned AS
user_is_banned\nFROM "user"\nWHERE "user".id != "user".id AND
"user".time_last_activity > 2011-10-21 17:36:21.577026 ORDER BY
"user".time_last_activity DESC\n LIMIT 3 '

because datetime must be '2011-10-21 17:36:21.577026'

Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: sqlalchemy from_statement dynamic attributes for python objects instances

2011-10-19 Thread lestat 
No, it not just 99.

It generated by difficult RAW SQL with hierarchy library
https://github.com/marplatense/sqla_hierarchy  and with recursive SQL.

FULL SQL is like  http://pastebin.com/b0wGUegy

It adds additional data as level, connect_path, is_leaf.

I get this SQL from hierarchy library and use it in from_statement for
generating Comment instances with eager loading of User table.

I want only add level to python instances objects from RAW SQL.

I tried add this like add new Mapper: http://pastebin.com/49vnqYJR
in populate_state function:
row_tuple = tuple(row)
level = row_tuple[-3]
dict_['level'] = level

and use it:
hierarchy_mapper = HierarchyMapper(model_class, model_class.__table__,
non_primary=True)
result =
db.session.query(hierarchy_mapper).from_statement(hie_statement)

where hie_statement is RAW SQL.

but I can't add options contains_eager like
result = result.options(contains_eager(Comment.user))

it print error:
ArgumentError: Can't find property 'user' on any entity specified in
this Query.  Note the full path from root (Mapper|Comment|comment|non-
primary) to target entity must be specified.
because it non_primary mapper.

How I can solve this problem?

Thanks!


On Oct 19, 7:04 pm, Michael Bayer  wrote:
> On Oct 19, 2011, at 8:19 AM, lestat wrote:
>
> > I have such model:
>
> > class Test(db.Model):
> >    __tablename__ = 'test'
>
> >    id = db.Column(db.Integer, primary_key=True)
>
> >    subject = db.Column(db.String(512), nullable=False)
>
> >    level = None
>
> arbitrary SQL expressions can be added to a mapping as described 
> athttp://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions   
>  
>
> That said if it were really just "99" I'd do that at the Python level; 
> assuming you need some DB functionality within "level".

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] sqlalchemy from_statement dynamic attributes for python objects instances

2011-10-19 Thread lestat 
I have such model:

class Test(db.Model):
__tablename__ = 'test'

id = db.Column(db.Integer, primary_key=True)

subject = db.Column(db.String(512), nullable=False)

level = None



Some code generate RAW SQL with additional dynamic data.

For example it like:
db.session.query(Test).from_statement("SELECT test.id AS test_id,
test.subject AS test_subject, 99 AS level FROM test").all()

It generate list of python objects , but how I can fill attribute
"additional" for these python objects?

I don't want store NULL column "additional" in database.

I want create dynamic additional data with SQL and add it to python
objects.

I tried add temporary column to table (but I not want store it in db)
like this:

from sqlalchemy.schema import Column
from sqlalchemy.types import Integer

level_column = Column('level', Integer)
Test.__table__.append_column(level_column)

but if I do:
db.session.query(Test).from_statement("SELECT test.id AS test_id,
test.subject AS test_subject, 99 AS level FROM test").all()

this objects don't have filled with level 99, it has level = None.


Help please.

Thanks


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.