Re: [sqlalchemy] case_convention in db python code

2011-01-06 Thread Petr Kobalíček 
Hi Michael,

I didn't find an example of this macro feature so I'm going to stay
with my solution at this time:)

Thank you anyway!
Petr

On Wed, Jan 5, 2011 at 9:52 PM, Michael Bayer mike...@zzzcomputing.com wrote:
 You can apply the macro at the Column, Table, or mapper() level, but yeah 
 pretty much you need to define the name munging code you want, and you need 
 to embed it somewhere between your configuration and the schema/ORM the way 
 you are doing here.

 Here's an alternate name mangler in case its useful:

 def uncamelize(text):
    def downcase(matchobj):
        return _ + matchobj.group(0).lower()
    if text:
        text = text[0].lower() + re.sub(r'([A-Z])', downcase, text[1:])
    return text


 On Jan 5, 2011, at 2:41 PM, Petr Kobalíček wrote:

 Hi,

 I'm using underscored_separator_convention in a database, but
 camelCase in python code.

 I created small wrapper across a table column so I don't need to
 define columns using a key property:

 from sqlalchemy import Column as SqlColumn

 def underscored(s):
  result = u
  for i in xrange(0, len(s)):
    c = s[i]
    if (c.isupper()):
      if i  0: result += u_
      result += c.lower()
    elif c.isdigit():
      if len(result) and result[-1].isdigit():
        result += c
      else:
        result += u_ + c
    else:
      result += c

  return result

 def Column(*args, **kw):
  k = args[0]
  m = list(args)
  m[0] = underscored(k)
  return SqlColumn(key=k, *m, **kw)

 Usage:
  Column(productId, BigInteger, Sequence(ux_address_id_seq),
 primary_key=True),
 This will create a table column product_id, but I access it as
 productId in python and sqlalchemy expression code.

 My question: Is this code good or there is a better way directly built
 into the sqlalchemy?

 Thanks!
 Petr

 --
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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.


 --
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] case_convention in db python code

2011-01-05 Thread Petr Kobalíček 
Hi,

I'm using underscored_separator_convention in a database, but
camelCase in python code.

I created small wrapper across a table column so I don't need to
define columns using a key property:

from sqlalchemy import Column as SqlColumn

def underscored(s):
  result = u
  for i in xrange(0, len(s)):
c = s[i]
if (c.isupper()):
  if i  0: result += u_
  result += c.lower()
elif c.isdigit():
  if len(result) and result[-1].isdigit():
result += c
  else:
result += u_ + c
else:
  result += c

  return result

def Column(*args, **kw):
  k = args[0]
  m = list(args)
  m[0] = underscored(k)
  return SqlColumn(key=k, *m, **kw)

Usage:
  Column(productId, BigInteger, Sequence(ux_address_id_seq),
primary_key=True),
This will create a table column product_id, but I access it as
productId in python and sqlalchemy expression code.

My question: Is this code good or there is a better way directly built
into the sqlalchemy?

Thanks!
Petr

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: Read only property

2010-08-31 Thread Petr Kobalíček 
Hi Mark,

I think I badly described what I'm doing. My problem is that I need to
update some columns atomically and I'm using stored procedures for
that. I'm still learning the postgres so maybe I'm doing so complex
things for so simple tasks.

As a very short and simple example:

RecordTable:
  id INT (primary)
  idx INT
  message STRING

RecordClass:
  ...

index ix index of message in the message list. Now I can fetch all
messages from the database in correct order. Then I have some methods
in mapped class, for example

def moveUp(self):
  ...

Here I'm calling stored procedure record_move_up() and I know that
index will be changed to index - 1 (in the most cases) so I'd like to
change the idx property in mapped class too without making the change
to the database again.

And now I wonder how to do it. I used sqlalchemy from 0.3-0.4 and I
noticed that SqlAlchemy is updating always everything. Now I noticed
that this is probably no longer true so if I do not touch the 'idx'
everything should be fine.

I'd like to post working example to my blog, but this will take some
time to finish.

Best regards
Petr

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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 write custom DDL class

2010-08-31 Thread Petr Kobalíček 
Hi,

I have problem to understand which way should be used to write custom
DDL class. I'm generating some stored procedures for my tables. The
procedures are very similar and I'd like to use some generic way. Now
I'm nearly done, but I need some way how to extract some information
from table to use it in my DDL.

I discovered that for this I need the engine where DDL runs.

My custom class may look like this:

class CustomDDL(DDL):
  def against(self, target):
# Is this the method I should generate the DDL? How can I access
engine from here?
self.target = target

There is also possibility to create a __call__ method where the engine
is, but I'm not sure if this is the right place.

So my questions:

- which method I should override to make my custom DDL substitution
- do I need to base class on DDL or DDLElement? I can create my own,
but I'm not sure if I can add it.



Currently I'm using something like this to inject some triggers/functions:

_DDL_MATCHER = re.compile(ur\$\{(_\w+)\:{0,1}([^\}]*)\})

class CustomDDL(object):
  def __init__(self, event, ddl):
self.event = event
self.ddl = ddl

  def inject(self, table):
DDL(self.compile(table)).execute_at(self.event, table)

  def compile(self, table):
global _DDL_MATCHER
def repl(match):
  func = match.group(1)
  args = match.group(2)
  return getattr(self, func)(table, args)
return _DDL_MATCHER.sub(repl, self.ddl);

  def _TABLE(self, table, args):
return table.name + args

  def _PREPARE_DECLARATION(self, table, args):
result = u
if hasattr(table, area):
  for column in table.area:
#result += ucondition_ + unicode(column.name) + u  + \
#  unicode(column.type.get_dbapi_type(engine.dialect.dbapi)) + u; 
pass
return result

  def _PREPARE_CONDITION(self, table, args):
return 

  def _WHERE(self, table, args):
if args:
  return uWHERE  + args
else:
  return u

This works for me, except I need to access the engine.

Thanks for any info about this

Best regards
Petr

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: how to write custom DDL class

2010-08-31 Thread Petr Kobalíček 
I solved everything by overriding against()

_DDL_MATCHER = re.compile(ur\$\{(_\w+)\:{0,1}([^\}]*)\})

class CustomDDL(DDL):
  def __init__(self, statement, on=None, context=None, bind=None):
super(CustomDDL, self).__init__(statement, on, context, bind)

  def against(self, target):
global _DDL_MATCHER
def repl(match):
  func = match.group(1)
  args = match.group(2)
  return getattr(self, func)(target, args)
return _DDL_MATCHER.sub(repl, self.statement);

This works for me.

I'm getting engine from target.metadata.bind and I hope that it's correct way.

On Wed, Sep 1, 2010 at 2:15 AM, Petr Kobalíček kobalicek.p...@gmail.com wrote:
 Hi,

 I have problem to understand which way should be used to write custom
 DDL class. I'm generating some stored procedures for my tables. The
 procedures are very similar and I'd like to use some generic way. Now
 I'm nearly done, but I need some way how to extract some information
 from table to use it in my DDL.

 I discovered that for this I need the engine where DDL runs.

 My custom class may look like this:

 class CustomDDL(DDL):
  def against(self, target):
    # Is this the method I should generate the DDL? How can I access
 engine from here?
    self.target = target

 There is also possibility to create a __call__ method where the engine
 is, but I'm not sure if this is the right place.

 So my questions:

 - which method I should override to make my custom DDL substitution
 - do I need to base class on DDL or DDLElement? I can create my own,
 but I'm not sure if I can add it.



 Currently I'm using something like this to inject some triggers/functions:

 _DDL_MATCHER = re.compile(ur\$\{(_\w+)\:{0,1}([^\}]*)\})

 class CustomDDL(object):
  def __init__(self, event, ddl):
    self.event = event
    self.ddl = ddl

  def inject(self, table):
    DDL(self.compile(table)).execute_at(self.event, table)

  def compile(self, table):
    global _DDL_MATCHER
    def repl(match):
      func = match.group(1)
      args = match.group(2)
      return getattr(self, func)(table, args)
    return _DDL_MATCHER.sub(repl, self.ddl);

  def _TABLE(self, table, args):
    return table.name + args

  def _PREPARE_DECLARATION(self, table, args):
    result = u
    if hasattr(table, area):
      for column in table.area:
        #result += ucondition_ + unicode(column.name) + u  + \
        #  unicode(column.type.get_dbapi_type(engine.dialect.dbapi)) + u; 
        pass
    return result

  def _PREPARE_CONDITION(self, table, args):
    return 

  def _WHERE(self, table, args):
    if args:
      return uWHERE  + args
    else:
      return u

 This works for me, except I need to access the engine.

 Thanks for any info about this

 Best regards
 Petr


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to write custom DDL class

2010-08-31 Thread Petr Kobalíček 
Hi Michael,

many thanks for explanation.

I considered what you wrote and now I'm making my own class as a
callable and I'm going to use append_ddl_listener(). This means I
don't need DDLElement and DDL.

Best regards
Petr

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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 get a column type from a column instance

2010-08-31 Thread Petr Kobalíček 
Hi devs,

how to get column type from a column instance?

Imagine following table:

RecordTable = Table('t_record', metadata,
  Column('record_id', Integer, primary_key = True),
  Column('dep', Integer, default=None)
)

I can get my column using:

engine = engine_from_config({
  sqlalchemy.url: postgresql:///,
  sqlalchemy.convert_unicode: True,
  sqlalchemy.echo: True
}, prefix=sqlalchemy.)

metadata = MetaData()
metadata.bind = engine

column = RecordTable.c.dep

Now I'd like to print (for example) 'column' type for 'engine'. This
is same type which will be used when I use metadata.create_all().

The best I can print is:
  print column.type.get_dbapi_type(engine.dialect.dbapi).name
which prints me 'NUMBER', but I'm expecting 'INT' (postgres).

Best regards
Petr Kobalicek

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] How to get a column type from a column instance

2010-08-31 Thread Petr Kobalíček 
Thanks again!

You saved me really a lot of time.

Best regards
Petr Kobalicek

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: Read only property

2010-08-30 Thread Petr Kobalíček 
I have small question regarding the viewonly property. Can I assign
viewonly to a column? In the SqlA example it's used together with
relationship(), I need to mark viewonly just table column.

For example:

RecordTable = Table('t_record', metadata,
  Column('record_id', Integer, primary_key = True),
  Column('idx', Integer, default=None),
  Column('msg', UnicodeText)
)

class RecordModel(object):
  def __init__(self, msg = u):
self.msg = msg

  def __repr__(self):
return uREC - Idx( + unicode(self.idx) + u), Msg( + self.msg + u)

orm.mapper(RecordModel, RecordTable,
  properties = {
id: RecordTable.c.record_id,
idx: RecordTable.c.idx,   MARK ReadOnly? 
msg: RecordTable.c.msg
  }
)

Thanks a lot

Best regards
Petr Kobalicek

On Mon, Aug 30, 2010 at 4:51 AM, Petr Kobalíček
kobalicek.p...@gmail.com wrote:
 Hi devs,

 thanks for replies!

 Michael: I asked probably wrong question. I know how to make property
 read-only in Python, but I wanted the property to be read-only at ORM
 level (so it will not appear in UPDATE).

 Mark: This is probably is solution to my problem.

 I'm trying to create an abstract wrapper for ordered-list records. I
 wanted to post example which describes what I'm doing, but I will
 create another topic when basic features will be working.

 Best regards
 Petr

 On Mon, Aug 30, 2010 at 3:59 AM, Mark zhengha...@gmail.com wrote:
 Hi Petr,

 You may want to have a look at this link
 http://www.sqlalchemy.org/docs/mappers.html#building-query-enabled-properties

 Setting the viewonly=True property in your mapper configuration does
 exactly what you want.  I've used it a couple of times in my own
 project and it works.

 -Mark

 On Aug 29, 10:00 pm, Petr Kobalíček kobalicek.p...@gmail.com wrote:
 Hi devs,

 is there a simple way how to make an ORM property read only? I need to
 fetch the column from database so it will be accessible through the
 mapped class, but I need that the property will be never updated back
 to the database (in case I add the object to the session and do
 commit).

 Thanks!

 Best regards
 Petr Kobalicek

 --
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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.




-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] DDL and TypeError: 'dict' object does not support indexing

2010-08-30 Thread Petr Kobalíček 
Hi devs,

I added %% into my DDL and sqlalchemy raises the TypeError:  'dict'
object does not support indexing.

The critical part is:

 row_array t_record%%ROWTYPE in the DDL.

Em I using the DDL correctly? I read that I need to double the '%'
character and I did that.

Best regards
Petr Kobalicek




The callable code is here (using postgres):
--

#!/usr/bin/env python

from sqlalchemy import MetaData
from sqlalchemy import Table, Column

from sqlalchemy import Integer, Boolean
from sqlalchemy import Unicode, UnicodeText
from sqlalchemy import DDL

from sqlalchemy import engine_from_config
from sqlalchemy import func
from sqlalchemy import select

from sqlalchemy import orm
from sqlalchemy import text

metadata = MetaData()

RecordTable = Table('t_record', metadata,
  Column('record_id', Integer, primary_key = True),
  Column('idx', Integer, default=None),
  Column('msg', UnicodeText)
)

sql_create = DDL(
  \n

  CREATE OR REPLACE FUNCTION t_record_new()\n
RETURNS trigger AS\n
  $BODY$\n
BEGIN\n
  NEW.idx := (SELECT COUNT(r.idx) AS t FROM t_record AS r);\n
  RETURN NEW;\n
END;\n
  $BODY$\n
  LANGUAGE 'plpgsql';\n

  \n

  CREATE OR REPLACE FUNCTION t_record_up(param_id BIGINT)\n
RETURNS void AS\n
  $BODY$\n
DECLARE\n
  r INT;\n
  row_array t_record%%ROWTYPE;\n
BEGIN\n
  -- Get index of the row we need to move.\n
  SELECT t_record.idx\n
FROM t_record\n
WHERE t_record.record_id = $1\n
INTO r;\n
  \n
  FOR row_array IN SELECT *\n
FROM t_record\n
WHERE t_record.idx = r\n
ORDER_BY t_record.idx\n
LIMIT 2\n
  LOOP\n
\n
  END LOOP;\n
  \n
  UPDATE t_record\n
SET idx=111\n
WHERE t_record.record_id = $1;\n
  \n
  RETURN;\n
END;\n
  $BODY$\n
  LANGUAGE 'plpgsql';\n
  \n

  CREATE TRIGGER t_record_new BEFORE INSERT ON t_record\n
  FOR EACH ROW\n
EXECUTE PROCEDURE t_record_new();\n
)
sql_create.execute_at('after-create', RecordTable)

sql_drop = DDL(
  \n +

  DROP TRIGGER IF EXISTS t_record_new ON t_record;\n +
  DROP FUNCTION IF EXISTS t_record_new();\n
)
sql_drop.execute_at('before-drop', RecordTable)

class RecordModel(object):
  def __init__(self, msg = u):
self.msg = msg

  def __repr__(self):
return uREC - Idx( + unicode(self.idx) + u), Msg( + self.msg + u)

def printRecords():
  print u\nRECORDS:
  rows = session.execute(select([RecordTable])).fetchall()
  for row in rows: \
print uREC - Idx( + unicode(row.idx) + u), Msg( + row.msg + u)

orm.mapper(RecordModel, RecordTable,
  properties = {
id: RecordTable.c.record_id,
msg: RecordTable.c.msg
  }
)

engine = engine_from_config({
  sqlalchemy.url: postgresql://someuser:somep...@localhost/somedb,
  sqlalchemy.convert_unicode: True,
  sqlalchemy.echo: True
}, prefix=sqlalchemy.)
metadata.bind = engine

metadata.drop_all(checkfirst=True)
metadata.create_all()

sm = orm.sessionmaker(bind = engine, autoflush = True)
session = orm.scoped_session(sm)




The full error log:
--

Traceback (most recent call last):
  File C:\My\Devel\Web\Test\sqla_readonly.py, line 114, in module
metadata.create_all()
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\schema
.py, line 2013, in create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py, line 1647, in create
connection=connection, **kwargs)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py, line 1682, in _run_visitor
**kwargs).traverse_single(element)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\sql\vi
sitors.py, line 77, in traverse_single
return meth(obj, **kw)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\ddl.py, line 42, in visit_metadata
self.traverse_single(table, create_ok=True)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\sql\vi
sitors.py, line 77, in traverse_single
return meth(obj, **kw)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\ddl.py, line 65, in visit_table
listener('after-create', table, self.connection)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\schema
.py, line 2200, in __call__
return bind.execute(self.against(target))
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py, line 1157, in execute
params)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py, line 1210, in _execute_ddl
return self.__execute_context(context)
  File C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py, line 1268, in __execute_context
context.parameters[0], context=context)
  File

Re: [sqlalchemy] DDL and TypeError: 'dict' object does not support indexing

2010-08-30 Thread Petr Kobalíček 
Hi Michael,

thanks for reply, I tried triple escaping before I posted the first
message, but the error is the same.

Interesting is fact that i can add 20 '%' characters into the DDL but
in SQL INFO I always see only one '%'.

Is there other workaround? Can I safely replace the line described in:
  http://www.sqlalchemy.org/trac/ticket/1897
?

Best regards
Petr Kobalicek

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] DDL and TypeError: 'dict' object does not support indexing

2010-08-30 Thread Petr Kobalíček 
Hi Michael,

triple escaping works, sorry for misinformation.

Best regards
Petr

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Read only property

2010-08-29 Thread Petr Kobalíček 
Hi devs,

is there a simple way how to make an ORM property read only? I need to
fetch the column from database so it will be accessible through the
mapped class, but I need that the property will be never updated back
to the database (in case I add the object to the session and do
commit).

Thanks!

Best regards
Petr Kobalicek

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Help with select where clause is more than one tag

2010-06-28 Thread Petr Kobalíček 
Hi devs,

I need to write a query where two tags match one product. I wrote
simple test-case I'm using with sqlite:



#!/usr/bin/env python
from sqlalchemy import *
from sqlalchemy.sql import and_, or_

engine = create_engine(sqlite://, echo=True)
metadata = MetaData(engine)

TagTable = Table(
  tag, metadata,

  Column(tag_id , Integer  , primary_key=True),
  Column(tag_name   , Unicode(128) , nullable=False)
)

ProductTable = Table(
  product, metadata,

  Column(product_id , Integer  , primary_key=True),
  Column(product_name   , Unicode(128) , nullable=False)
)

TagToProductTable = Table(
  tag_to_product, metadata,

  Column(tag_id , Integer  ,
ForeignKey(ProductTable.c.product_id), nullable=False,
primary_key=True),
  Column(product_id , Integer  ,
ForeignKey(TagTable.c.tag_id), nullable=False, primary_key=True)
)

if __name__ == '__main__':
  metadata.create_all()

  engine.execute(TagTable.insert(values={tag_name: uTag 1}))
  engine.execute(TagTable.insert(values={tag_name: uTag 2}))

  engine.execute(ProductTable.insert(values={product_name: Product A}))
  engine.execute(ProductTable.insert(values={product_name: Product B}))

  engine.execute(TagToProductTable.insert(values={tag_id: 1,
product_id: 1}))
  engine.execute(TagToProductTable.insert(values={tag_id: 2,
product_id: 1}))

  print --
  result = engine.execute(
select([ProductTable],
  select([func.count(TagToProductTable.c.tag_id)],
or_(TagToProductTable.c.tag_id==1, TagToProductTable.c.tag_id==2)
  ).correlate(TagToProductTable) == 2
)
  ).fetchall()
  print --

  for item in result:
print item.product_name

  metadata.drop_all()

Current code will raise OperationalError: no such column: False

Problem is that I not understand how to write the sub-select. I know
that there are other solutions than sub-select, but I'd like to
understand this method first.

I'm using latest SqlAlchemy and Python. Any hint will be appreciated;)

NOTE: I read the manual and API documentation, but it not helped me,
is there some SQLA example where something like this is used?

-- 
Best regards
- Petr Kobalicek http://kobalicek.com

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Help with select where clause is more than one tag

2010-06-28 Thread Petr Kobalíček 
okay,

my select without sub-select looks like this:

  f = func.count(TagToProductTable)
  result = engine.execute(
select([ProductTable, TagToProductTable, f]).where(
  and_(TagToProductTable.c.product_id == ProductTable.c.product_id,
or_(TagToProductTable.c.tag_id==1, TagToProductTable.c.tag_id==2))).\
group_by(ProductTable.c.product_id).\
having(f == 2)
  ).fetchall()

It does what I need, selects all products that have assigned tag with
id 1 and 2. If I find the sub-select solution I will paste it here.

The question is:

If I'm building select programatically and I have:

   query = select([ProductTable])

   ...

How can I extend this query by the query shown above?

Thanks
- Petr

On Mon, Jun 28, 2010 at 9:40 AM, Petr Kobalíček
kobalicek.p...@gmail.com wrote:
 Hi devs,

 I need to write a query where two tags match one product. I wrote
 simple test-case I'm using with sqlite:

 

 #!/usr/bin/env python
 from sqlalchemy import *
 from sqlalchemy.sql import and_, or_

 engine = create_engine(sqlite://, echo=True)
 metadata = MetaData(engine)

 TagTable = Table(
  tag, metadata,

  Column(tag_id             , Integer      , primary_key=True),
  Column(tag_name           , Unicode(128) , nullable=False)
 )

 ProductTable = Table(
  product, metadata,

  Column(product_id         , Integer      , primary_key=True),
  Column(product_name       , Unicode(128) , nullable=False)
 )

 TagToProductTable = Table(
  tag_to_product, metadata,

  Column(tag_id             , Integer      ,
 ForeignKey(ProductTable.c.product_id), nullable=False,
 primary_key=True),
  Column(product_id         , Integer      ,
 ForeignKey(TagTable.c.tag_id), nullable=False, primary_key=True)
 )

 if __name__ == '__main__':
  metadata.create_all()

  engine.execute(TagTable.insert(values={tag_name: uTag 1}))
  engine.execute(TagTable.insert(values={tag_name: uTag 2}))

  engine.execute(ProductTable.insert(values={product_name: Product A}))
  engine.execute(ProductTable.insert(values={product_name: Product B}))

  engine.execute(TagToProductTable.insert(values={tag_id: 1,
 product_id: 1}))
  engine.execute(TagToProductTable.insert(values={tag_id: 2,
 product_id: 1}))

  print --
  result = engine.execute(
    select([ProductTable],
      select([func.count(TagToProductTable.c.tag_id)],
        or_(TagToProductTable.c.tag_id==1, TagToProductTable.c.tag_id==2)
      ).correlate(TagToProductTable) == 2
    )
  ).fetchall()
  print --

  for item in result:
    print item.product_name

  metadata.drop_all()

 Current code will raise OperationalError: no such column: False

 Problem is that I not understand how to write the sub-select. I know
 that there are other solutions than sub-select, but I'd like to
 understand this method first.

 I'm using latest SqlAlchemy and Python. Any hint will be appreciated;)

 NOTE: I read the manual and API documentation, but it not helped me,
 is there some SQLA example where something like this is used?

 --
 Best regards
 - Petr Kobalicek http://kobalicek.com


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] many to many join condition

2009-12-02 Thread Petr Kobalíček 
Hi devs,

I have one problem related to join and adding conditions (sorry for
misleading subject, I don't know how to describe it shortly).

I have two tables:
  Product
  Tag

There is many to many relation between tag and products and I need to
select product where two tags are set. How to do this using
SqlAlchemy?

Here are table definitions:


ProductTable = sql.Table(
  Product, meta.metadata,

  sql.Column(productId   , sql.Integer  , primary_key=True),

  ... (not important)
)

TagTable = sql.Table(
  Tag, meta.metadata,

  sql.Column(tagId   , sql.Integer  , primary_key=True),

  ... (not important)
)

TagToProductTable = sql.Table(
  TagToProduct, meta.metadata,

  sql.Column(tagId   , sql.Integer  ,
sql.ForeignKey(Tag.tagId), nullable=False),
  sql.Column(productId   , sql.Integer  ,
sql.ForeignKey(Product.productId), nullable=False)
)

My mappers configuration:


orm.mapper(Product, Product._table,
  properties={
tags: orm.relation(Tag, secondary=TagToProductTable),
  }
)

orm.mapper(Tag, Tag._table,
  properties={}
)


Currently I can filter products by ONE tag using join, the query looks
like this:

  Session().query(Product).join(Product.tags, TagTable.c.tagId ==
tagId).filter(...).order_by(...)

If I want to filter by firstTag AND secondTag this of course not
works. Is here any easy solution for this problem?

Thanks for ideas

-- 
Best regards
- Petr Kobalicek http://kobalicek.com

--

You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: inserting

2008-11-26 Thread Petr Kobalíček 

So, I can use that way in inserting one row, but can't when inserting
multiple rows ? It is correct ?

2008/11/24 Michael Bayer [EMAIL PROTECTED]:

 oh, right.  Column objects only work when you say insert().values(**dict).


 MikeCo wrote:

 Using 0.5.0rc4 doesn't seem to do that. or what am I doing wrong?

 The test, http://pastebin.com/fd0653b0 , looks like when using the
 Column object, the values inserted are all None (test 1). When the key
 is the fully qualified table.column, the value inserted is always
 the default value for the column (test 3). It only works correct when
 the key is the string for the unqualified column name (tests 2 and 4).


 On Nov 24, 10:37 am, Michael Bayer [EMAIL PROTECTED] wrote:
 the actual Column object or its key can be placed in the dict.

 MikeCo wrote:

  Oops, not quite right. str(table.c.colname) returns 'table.colname,
  and that doesn't work right as dictionary key. You need col only as
  dictionary key.

 http://pastebin.com/fd0653b0 has some tests

  Interesting question is does SA intend that table.colname work in
  the dictionary definition?

  --
  Mike

  On Nov 23, 8:58 am, MikeCo [EMAIL PROTECTED] wrote:
  Your dictionary key CartItemTable.c.colname is an instance of class
  Column, The dictionary keys need to be strings. Use str
  (CartItemTable.c.colname) to get the string name of the column and it
  should work.

   CartItemTable.c.userId

  Column('userId', Integer(), ForeignKey('User.userId'),
  table=CartItem, primary_key=True, nullable=False)
  str(CartItemTable.c.userId)

  'CartItem.userId'

  --
  Mike

  On Nov 23, 8:12 am, Petr Kobalíèek [EMAIL PROTECTED]
 wrote:

   Hi devs,

   I don't understand one thing:

   I have table:

   CartItemTable = sql.Table(
 CartItem, meta.metadata,

 # Relations
 sql.Column(userId  , sql.Integer  ,
   sql.ForeignKey(User.userId), nullable=False, primary_key=True),
 sql.Column(productId   , sql.Integer  ,
   sql.ForeignKey(Product.productId), nullable=False,
   primary_key=True),
 sql.Column(variantId   , sql.Integer  ,
 nullable=True,
   default=None),

 # Count of items in shopping cart
 sql.Column(count   , sql.Integer  ,
   nullable=False, default=1)
   )

   and I want to insert multiple rows to it using sql:

 Session().execute(
   CartItemTable.insert(),
   [{
 CartItemTable.c.userId: self.user.userId,
 CartItemTable.c.productId : item.product.productId,
 CartItemTable.c.variantId : vid(item.variant),
 CartItemTable.c.count : item.count
   } for item in self.items]
 )

   But this not works and I must use this way:

 Session().execute(
   CartItemTable.insert(),
   [{
 userId: self.user.userId,
 productId : item.product.productId,
 variantId : vid(item.variant),
 count : item.count
   } for item in self.items]
 )

   Why is not working first syntax, what em I missing ?

   Cheers
   - Petr
 



 


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] inserting

2008-11-23 Thread Petr Kobalíček 

Hi devs,

I don't understand one thing:

I have table:

CartItemTable = sql.Table(
  CartItem, meta.metadata,

  # Relations
  sql.Column(userId  , sql.Integer  ,
sql.ForeignKey(User.userId), nullable=False, primary_key=True),
  sql.Column(productId   , sql.Integer  ,
sql.ForeignKey(Product.productId), nullable=False,
primary_key=True),
  sql.Column(variantId   , sql.Integer  , nullable=True,
default=None),

  # Count of items in shopping cart
  sql.Column(count   , sql.Integer  ,
nullable=False, default=1)
)

and I want to insert multiple rows to it using sql:

  Session().execute(
CartItemTable.insert(),
[{
  CartItemTable.c.userId: self.user.userId,
  CartItemTable.c.productId : item.product.productId,
  CartItemTable.c.variantId : vid(item.variant),
  CartItemTable.c.count : item.count
} for item in self.items]
  )

But this not works and I must use this way:

  Session().execute(
CartItemTable.insert(),
[{
  userId: self.user.userId,
  productId : item.product.productId,
  variantId : vid(item.variant),
  count : item.count
} for item in self.items]
  )

Why is not working first syntax, what em I missing ?

Cheers
- Petr

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Postgres - Backup - Restore

2008-11-10 Thread Petr Kobalíček 
Hi Chris,

yeah these tools works great, our problem was that if I did backup and
restoration from web interface then this problem happen.

I wasn't also familiar with postgres :)

Cheers
- Petr

2008/11/10 Chris Miles [EMAIL PROTECTED]:


 On Nov 10, 4:57 am, Petr Kobalíček [EMAIL PROTECTED] wrote:
 I have postgres related problem. I'm normally developing with sqlite,
 but we are using postgres on the server. The problem is that
 sqlalchemy probably remembers primary keys and after database restore
 it will start in all tables from 1.

 If you use the PostgreSQL tools pg_dump and pg_restore they should
 maintain the sequences properly for you when copying databases between
 servers.

 Cheers,
 Chris Miles


 


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Postgres - Backup - Restore

2008-11-09 Thread Petr Kobalíček 
Thank you Michael.

2008/11/9 Michael Bayer [EMAIL PROTECTED]:

 you need to call ALTER SEQUENCE on your sequences such that they begin
 with an integer identifier greater than that of the table they are
 being used with.


 On Nov 9, 2008, at 1:22 PM, Petr Kobalíček wrote:

 I have found some material about this and this is called 'sequences'
 in postgres terminology.

 So I know the problem, but I don't know how to synchronize sequences
 using sqlalchemy.

 Cheers
 - Petr

 2008/11/9 Petr Kobalíček [EMAIL PROTECTED]:
 Hi devs,

 I have postgres related problem. I'm normally developing with sqlite,
 but we are using postgres on the server. The problem is that
 sqlalchemy probably remembers primary keys and after database restore
 it will start in all tables from 1.

 The error is (IntegrityError) duplicate key violates unique
 constraint.

 I'm defining tables in way that's in documentation, we want to
 include
 more databases so we are not using specific DB extensions:

 # Example
 OrderTable = sql.Table(
 Order, meta.metadata,
 # Relation
 sql.Column(orderId , sql.Integer  ,
 primary_key=True),
 # If userId == None, the order is anonymous
 sql.Column(userId  , sql.Integer  ,
 sql.ForeignKey(User.userId), nullable=True),

 ...

 I don't know if this problem was discussed before, but I didn't found
 any informations about this.

 Cheers and thanks for replies:)
 - Petr


 


 


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Postgres - Backup - Restore

2008-11-09 Thread Petr Kobalíček 

Hi devs,

I have postgres related problem. I'm normally developing with sqlite,
but we are using postgres on the server. The problem is that
sqlalchemy probably remembers primary keys and after database restore
it will start in all tables from 1.

The error is (IntegrityError) duplicate key violates unique constraint.

I'm defining tables in way that's in documentation, we want to include
more databases so we are not using specific DB extensions:

# Example
OrderTable = sql.Table(
  Order, meta.metadata,
  # Relation
  sql.Column(orderId , sql.Integer  , primary_key=True),
  # If userId == None, the order is anonymous
  sql.Column(userId  , sql.Integer  ,
sql.ForeignKey(User.userId), nullable=True),

  ...

I don't know if this problem was discussed before, but I didn't found
any informations about this.

Cheers and thanks for replies:)
- Petr

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Postgres - Backup - Restore

2008-11-09 Thread Petr Kobalíček 
I have found some material about this and this is called 'sequences'
in postgres terminology.

So I know the problem, but I don't know how to synchronize sequences
using sqlalchemy.

Cheers
- Petr

2008/11/9 Petr Kobalíček [EMAIL PROTECTED]:
 Hi devs,

 I have postgres related problem. I'm normally developing with sqlite,
 but we are using postgres on the server. The problem is that
 sqlalchemy probably remembers primary keys and after database restore
 it will start in all tables from 1.

 The error is (IntegrityError) duplicate key violates unique constraint.

 I'm defining tables in way that's in documentation, we want to include
 more databases so we are not using specific DB extensions:

 # Example
 OrderTable = sql.Table(
  Order, meta.metadata,
  # Relation
  sql.Column(orderId , sql.Integer  , primary_key=True),
  # If userId == None, the order is anonymous
  sql.Column(userId  , sql.Integer  ,
 sql.ForeignKey(User.userId), nullable=True),

  ...

 I don't know if this problem was discussed before, but I didn't found
 any informations about this.

 Cheers and thanks for replies:)
 - Petr


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---