alex9999 | 1 Oct 07:46 2009
Picon

Re: NULL values sorting


order_by(func.coalesce
(items.c.code0, '00'))

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

Christian Démolis | 1 Oct 10:02 2009
Picon

Re: Orm slow to update why?

Hello,

I tried all the method to compare the different methods :

TEST CODE

            xref = time.time()
            self.UtilisateurCourant.Dispo = 1
            session.merge(self.UtilisateurCourant, dont_load=True)
            session.flush()
            print "With ORM dont_load", time.time()-xref
           
            xref = time.time()
            session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==self.UtilisateurCourant.IdUtilisateur).update({'Dispo':0}, False)
            print "With ORM force update", time.time()-xref
           
            xref = time.time()
            if self.UtilisateurCourant.IdUtilisateur:
                session.execute("UPDATE utilisateur SET Dispo=0 WHERE IdUtilisateur="+str(self.UtilisateurCourant.IdUtilisateur))
            print "With ORM pure SQL", time.time()-xref
           
            import MySQLdb
            db = MySQLdb.connection(host="192.168.45.28", user="apm", passwd="apm", db="test_christian")
            xref = time.time()
            db.query("""UPDATE utilisateur SET Dispo=1 WHERE IdUtilisateur=1""")
            r = db.store_result()
            print "With MySQLdb without ORM", time.time()-xref
       

TEST RESULTS

With ORM dont_load 0.453000068665
With ORM force update 0.296999931335
With ORM pure SQL 0.31200003624
With MySQLdb without ORM 0.0939998626709

With ORM dont_load 0.452999830246
With ORM force update 0.297000169754
With ORM pure SQL 0.31200003624
With MySQLdb without ORM 0.0939998626709

With ORM dont_load 0.453000068665
With ORM force update 0.296999931335
With ORM pure SQL 0.31200003624
With MySQLdb without ORM 0.0940001010895


2009/9/30 Michael Bayer <mike_mp <at> zzzcomputing.com>

Christian Démolis wrote:
> Thx for your answer.
> MakeReleased is a method of com object windows agent (self.agent =
> DispatchWithEvents('CosmoAgent.clsCCAgent', Evenement))
> It takes 0 second to execute as we can see in the execute print

yes I realized later there were two blocks of timer calls.  The inclusion
of that code made the example harder to read.

In particular using session.merge() with an object will issue a SELECT
first to locate the current row.   this is likely the cause of the
slowness in this specific case since you seem to have a slow network
(profiling would reveal this too).   Passing "dont_load=True" to merge()
will skip the SELECT step and trust that the state you are passing it is
the state that is within the database.

> "Orm does additional steps"
> is it possible to force him update only one attribute of the object?

you may say:

session.query(MyObject).filter(some_criterion).update({'attrname':somenewvalue},
False)

which will issue an UPDATE statement matching the criterion.  this is the
fastest way by far using the ORM only.

Changing the "False" to "evaluate" or "expire" will also update or expire
the state of your ORM instance - but that will add some overhead.






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

Andrey Semyonov | 1 Oct 10:46 2009
Picon

Combined type field and session handler


Hi, there.

Just trying to make SA work with combined type field.
First, I have such a type in PGSQL:

CREATE TYPE mytype AS
   (id integer,
    flags integer,
    degrees numeric[]);

Then I create a column in table 'mytable' as
ALTER TABLE mytable ADD COLUMN tmpcol mytype[];

Then I declare this column in mapper:
                          sa.Column("tmpcol", PGArray(PGmytype))

and define PGmytype in application library:
class PGmytype(sqltypes.TypeEngine, sqltypes.MutableType):
    def __init__(self):
        print "PGmytype.__init__"

But this makes it possible to only add or delete records into DB, but
not change. when I try to change a record in DB flush() returns
nothing.

What must be done for custom type to make it really mutable?
    def copy_value(self, value):
        print "copy_value  - %s" % repr(value)
        if value is None:
            return None
        else:
            return value

    def compare_values(self, x, y):
        print "compare_values  - %s %s" % (repr(x), repr(y))
        return x == y

    def is_mutable(self):
        print "is_mutable"
        return True

    def bind_processor(self, dialect):
        def process(value):
            print "bproc"
            return value
        return process

    def result_processor(self, dialect):
        def process(value):
            print "rproc"
            return value
        return process

    def get_col_spec(self):
        return 'mytype'

    def get_dbapi_type(self, dbapi):
        print "get_dbapi_type %s" % repr(dbapi)
        dbapi.mytype

    def dialect_impl(self, dialect):
        print "dialect_impl %s" % repr(dialect)
        return sqltypes.TypeEngine.dialect_impl(self, dialect)

class mytype(object):
    def __init__(self):
        self.id = None
        self.flags = None
        self.degrees = []

    def __repr__(self):
        degrees = ", ".join(["%s" % degree for degree in
self.degrees])
        res = '(%s, %s, ARRAY[%s])::mytype' % (self.id, self.flags,
degrees)
        return res

    def __eq__(self, value):
        print "__eq__"
        return (self.id == value.id) and (self.flags == value.flags)
and (self.degrees == value.degrees)

    def __cmp__(self, value):
        print "__cmp__"
        return 0 if ((self.id == value.id) and (self.flags ==
value.flags) and (self.degrees == value.degrees)) else 1

    def __ne__(self, value):
        return not((self.id == value.id) and (self.flags ==
value.flags) and (self.degrees == value.degrees))

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

Christian Démolis | 1 Oct 11:39 2009
Picon

Bypass checking to database structure (metadata.create_all)

Hi again,

Is there any way to avoid checking database structure during the metadata.create_all declaration's phase?
It can be good to check when we are in test phase but when we are in production and we are sure of our model, it can be good to bypass create_all checking to database.

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

King Simon-NFHD78 | 1 Oct 11:50 2009

Re: Bypass checking to database structure (metadata.create_all)


> -----Original Message-----
> From: sqlalchemy <at> googlegroups.com 
> [mailto:sqlalchemy <at> googlegroups.com] On Behalf Of Christian Démolis
> Sent: 01 October 2009 10:40
> To: sqlalchemy <at> googlegroups.com
> Subject: [sqlalchemy] Bypass checking to database structure 
> (metadata.create_all)
> 
> Hi again,
> 
> Is there any way to avoid checking database structure during 
> the metadata.create_all declaration's phase?
> It can be good to check when we are in test phase but when we 
> are in production and we are sure of our model, it can be 
> good to bypass create_all checking to database.
> 

create_all (and drop_all) have a 'checkfirst' parameter that defaults to True. If you set it to False, SA
won't check to see if your tables already exist before issuing the CREATE statements:

<http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData.create_all>

Hope that helps,

Simon

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

Christian Démolis | 1 Oct 12:25 2009
Picon

Re: Bypass checking to database structure (metadata.create_all)

Thx Simon,

I tried Base.metadata.create_all(engine, checkfirst=False)
but it throws an error.
When checkfirst is True, the declaration works.
I don t understand...

Maybe orm needs additional information in declarative classes when checkfirst=False because orm doesn t look in database in this case?

These is the error :

Z:\>python Declaration.py
Le temps de chargement des modules SQL ALCHEMY 0.25
Le temps de dÚclaration SQL ALCHEMY 0.156000137329
Traceback (most recent call last):
  File "Declaration.py", line 1435, in <module>
    Base.metadata.create_all(engine, checkfirst=False)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sche
ma.py", line 1796, in create_all
    bind.create(self, checkfirst=checkfirst, tables=tables)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 1129, in create
    self._run_visitor(self.dialect.schemagenerator, entity, connection=connectio
n, **kwargs)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 1158, in _run_visitor
    visitorcallable(self.dialect, conn, **kwargs).traverse(element)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
visitors.py", line 89, in traverse
    return traverse(obj, self.__traverse_options__, self._visitor_dict)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
visitors.py", line 200, in traverse
    return traverse_using(iterate(obj, opts), obj, visitors)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
visitors.py", line 194, in traverse_using
    meth(target)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
compiler.py", line 831, in visit_metadata
    self.traverse_single(table)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
visitors.py", line 79, in traverse_single
    return meth(obj)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
compiler.py", line 870, in visit_table
    self.execute()
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 1812, in execute
    return self.connection.execute(self.buffer.getvalue())
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 824, in execute
    return Connection.executors[c](self, object, multiparams, params)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 888, in _execute_text
    return self.__execute_context(context)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 896, in __execute_context
    self._cursor_execute(context.cursor, context.statement, context.parameters[0
], context=context)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 950, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
ne\base.py", line 931, in _handle_dbapi_exception
    raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidat
ed=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "Erreur de syntaxe pr
\xe8s de ' \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlongitude VARCHAR, \n\teloi
gn' \xe0 la ligne 3") '\nCREATE TABLE maps_ville (\n\t`IdVille` INTEGER NOT NULL
 AUTO_INCREMENT, \n\tnom VARCHAR, \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlong
itude VARCHAR, \n\teloignement VARCHAR, \n\turl VARCHAR, \n\tPRIMARY KEY (`IdVil
le`)\n)\n\n' ()



2009/10/1 King Simon-NFHD78 <simon.king <at> motorola.com>

> -----Original Message-----
> From: sqlalchemy <at> googlegroups.com
> [mailto:sqlalchemy <at> googlegroups.com] On Behalf Of Christian Démolis
> Sent: 01 October 2009 10:40
> To: sqlalchemy <at> googlegroups.com
> Subject: [sqlalchemy] Bypass checking to database structure
> (metadata.create_all)
>
> Hi again,
>
> Is there any way to avoid checking database structure during
> the metadata.create_all declaration's phase?
> It can be good to check when we are in test phase but when we
> are in production and we are sure of our model, it can be
> good to bypass create_all checking to database.
>

create_all (and drop_all) have a 'checkfirst' parameter that defaults to True. If you set it to False, SA won't check to see if your tables already exist before issuing the CREATE statements:

<http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData.create_all>

Hope that helps,

Simon




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

limodou | 1 Oct 12:54 2009
Picon

Re: Bypass checking to database structure (metadata.create_all)


On Thu, Oct 1, 2009 at 6:25 PM, Christian Démolis
<christiandemolis <at> gmail.com> wrote:
> Thx Simon,
>
> I tried Base.metadata.create_all(engine, checkfirst=False)
> but it throws an error.
> When checkfirst is True, the declaration works.
> I don t understand...
>
> Maybe orm needs additional information in declarative classes when
> checkfirst=False because orm doesn t look in database in this case?
>
> These is the error :
>
> Z:\>python Declaration.py
> Le temps de chargement des modules SQL ALCHEMY 0.25
> Le temps de dÚclaration SQL ALCHEMY 0.156000137329
> Traceback (most recent call last):
>   File "Declaration.py", line 1435, in <module>
>     Base.metadata.create_all(engine, checkfirst=False)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sche
> ma.py", line 1796, in create_all
>     bind.create(self, checkfirst=checkfirst, tables=tables)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 1129, in create
>     self._run_visitor(self.dialect.schemagenerator, entity,
> connection=connectio
> n, **kwargs)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 1158, in _run_visitor
>     visitorcallable(self.dialect, conn, **kwargs).traverse(element)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 89, in traverse
>     return traverse(obj, self.__traverse_options__, self._visitor_dict)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 200, in traverse
>     return traverse_using(iterate(obj, opts), obj, visitors)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 194, in traverse_using
>     meth(target)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> compiler.py", line 831, in visit_metadata
>     self.traverse_single(table)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 79, in traverse_single
>     return meth(obj)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> compiler.py", line 870, in visit_table
>     self.execute()
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 1812, in execute
>     return self.connection.execute(self.buffer.getvalue())
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 824, in execute
>     return Connection.executors[c](self, object, multiparams, params)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 888, in _execute_text
>     return self.__execute_context(context)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 896, in __execute_context
>     self._cursor_execute(context.cursor, context.statement,
> context.parameters[0
> ], context=context)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 950, in _cursor_execute
>     self._handle_dbapi_exception(e, statement, parameters, cursor, context)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 931, in _handle_dbapi_exception
>     raise exc.DBAPIError.instance(statement, parameters, e,
> connection_invalidat
> ed=is_disconnect)
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "Erreur de
> syntaxe pr
> \xe8s de ' \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlongitude VARCHAR,
> \n\teloi
> gn' \xe0 la ligne 3") '\nCREATE TABLE maps_ville (\n\t`IdVille` INTEGER NOT
> NULL
>  AUTO_INCREMENT, \n\tnom VARCHAR, \n\tcp VARCHAR, \n\tlatitude VARCHAR,
> \n\tlong
> itude VARCHAR, \n\teloignement VARCHAR, \n\turl VARCHAR, \n\tPRIMARY KEY
> (`IdVil
> le`)\n)\n\n' ()
>
>

It seems that there is no length for VARCHAR, the right syntax should
be VARCHAR(length), and I also need this problem when I testing in
Mysql, but there is no problem in Sqlite.

--

-- 
I like python!
UliPad <<The Python Editor>>: http://code.google.com/p/ulipad/
UliWeb <<simple web framework>>: http://uliwebproject.appspot.com
My Blog: http://hi.baidu.com/limodou

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

Julian Krause | 1 Oct 09:38 2009
Picon

Multiple Joined Inheritance Issues


I have looked all over to a solution for this issue and haven't found
a solution. I am trying to use inheritance more than one level deep
and it does not seem to set the discriminator field on the top level
item. It works fine if it is only subclassed once but when you try and
do it twice it fails. The code below shows this happening in as short
of an example as I could come up with.

Thanks in advance for help,

Julian Krause

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey,
create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Person(Base):
    __tablename__ = 'people'
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    __tablename__ = 'engineers'
    id = Column(Integer, ForeignKey('people.id'), primary_key=True)
    primary_language = Column(String(50))
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_identity': 'engineer',
'polymorphic_on': discriminator}

class MechanicalEngineer(Engineer):
    __tablename__ = 'mechanical_engineers'
    __mapper_args__ = {'polymorphic_identity': 'mechanical_engineer'}
    id = Column(Integer, ForeignKey('people.id'), primary_key=True)
    engineer_id = Column(Integer, ForeignKey('engineers.id'),
primary_key=True)
    primary_tool = Column(String(50))

engine = create_engine('sqlite:///test.db', echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)

sess = Session()

me = MechanicalEngineer()
sess.add(me)
sess.commit()

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

Christian Démolis | 1 Oct 14:13 2009
Picon

Re: Bypass checking to database structure (metadata.create_all)

Ok, i just realize that create_all is useless when database already exist.
Starting my application is 6 seconds faster now.

Thanks all

2009/10/1 limodou <limodou <at> gmail.com>

On Thu, Oct 1, 2009 at 6:25 PM, Christian Démolis
<christiandemolis <at> gmail.com> wrote:
> Thx Simon,
>
> I tried Base.metadata.create_all(engine, checkfirst=False)
> but it throws an error.
> When checkfirst is True, the declaration works.
> I don t understand...
>
> Maybe orm needs additional information in declarative classes when
> checkfirst=False because orm doesn t look in database in this case?
>
> These is the error :
>
> Z:\>python Declaration.py
> Le temps de chargement des modules SQL ALCHEMY 0.25
> Le temps de dÚclaration SQL ALCHEMY 0.156000137329
> Traceback (most recent call last):
>   File "Declaration.py", line 1435, in <module>
>     Base.metadata.create_all(engine, checkfirst=False)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sche
> ma.py", line 1796, in create_all
>     bind.create(self, checkfirst=checkfirst, tables=tables)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 1129, in create
>     self._run_visitor(self.dialect.schemagenerator, entity,
> connection=connectio
> n, **kwargs)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 1158, in _run_visitor
>     visitorcallable(self.dialect, conn, **kwargs).traverse(element)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 89, in traverse
>     return traverse(obj, self.__traverse_options__, self._visitor_dict)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 200, in traverse
>     return traverse_using(iterate(obj, opts), obj, visitors)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 194, in traverse_using
>     meth(target)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> compiler.py", line 831, in visit_metadata
>     self.traverse_single(table)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> visitors.py", line 79, in traverse_single
>     return meth(obj)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\
> compiler.py", line 870, in visit_table
>     self.execute()
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 1812, in execute
>     return self.connection.execute(self.buffer.getvalue())
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 824, in execute
>     return Connection.executors[c](self, object, multiparams, params)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 888, in _execute_text
>     return self.__execute_context(context)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 896, in __execute_context
>     self._cursor_execute(context.cursor, context.statement,
> context.parameters[0
> ], context=context)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 950, in _cursor_execute
>     self._handle_dbapi_exception(e, statement, parameters, cursor, context)
>   File
> "c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi
> ne\base.py", line 931, in _handle_dbapi_exception
>     raise exc.DBAPIError.instance(statement, parameters, e,
> connection_invalidat
> ed=is_disconnect)
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "Erreur de
> syntaxe pr
> \xe8s de ' \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlongitude VARCHAR,
> \n\teloi
> gn' \xe0 la ligne 3") '\nCREATE TABLE maps_ville (\n\t`IdVille` INTEGER NOT
> NULL
>  AUTO_INCREMENT, \n\tnom VARCHAR, \n\tcp VARCHAR, \n\tlatitude VARCHAR,
> \n\tlong
> itude VARCHAR, \n\teloignement VARCHAR, \n\turl VARCHAR, \n\tPRIMARY KEY
> (`IdVil
> le`)\n)\n\n' ()
>
>

It seems that there is no length for VARCHAR, the right syntax should
be VARCHAR(length), and I also need this problem when I testing in
Mysql, but there is no problem in Sqlite.


--
I like python!
UliPad <<The Python Editor>>: http://code.google.com/p/ulipad/
UliWeb <<simple web framework>>: http://uliwebproject.appspot.com
My Blog: http://hi.baidu.com/limodou




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

volx | 1 Oct 15:44 2009
Picon

Re: padding of CHAR fields, inconsistent where clause; Oracle example


Micheal:

Thanks for your help thus far. Unfortunately I don't think the
get_dbapi_type method gets called. From reading your code I see how it
should be called from pre_exec -> setinputsizes -> get_dbapi_type but
empirical evidence shows otherwise. Here's my little harness:

import sqlalchemy.types as sqltypes
from exceptions import NotImplementedError

class OracleChar(sqltypes.CHAR):
    """Patched OracleChar type to fix padding issue
    http://groups.google.com/group/sqlalchemy/browse_thread/thread/965287c91b790b68
    """
    def get_col_spec(self):
        return "CHAR(%(length)s)" % {'length' : self.length}

    def get_dbapi_type(self, dbapi):
        raise NotImplementedError('gotcha')
        return dbapi.FIXED_CHAR

from sqlalchemy.databases import oracle
oracle.OracleChar = OracleChar

from sqlalchemy.ext.sqlsoup import SqlSoup

dburl = "oracle://....."
soup = SqlSoup(dburl)
all = soup.price_sources.filter_by(desciption='EJV').all()
print all  # shows empty list, we should not even get here due to
exception I raise

all = soup.price_sources.filter_by
(desciption='EJV
').all()
print all  # shows record in list

Returns empty list.

On Sep 30, 3:44 pm, "Michael Bayer" <mike... <at> zzzcomputing.com> wrote:
> volx wrote:
>
> > cx_Oracle actually has thread on that a topic at
> >http://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.0...
>
> > It boils down to having to specify a type for input parameter. Is that
> > something I could "plug-in" as a decorator or would that need to be
> > hacked on SqlAlchemy level?
>
> here is the thing.  We call setinputsizes() with oracle.  So perhaps
> OracleChar() needs to have dbapi.FIXED_CHAR established as its
> get_dbapi_type().   would you care to try monkeypatching this (in 0.5, the
> fix for 0.6 would be different) ?
>
> class OracleChar(sqltypes.CHAR):
>     def get_col_spec(self):
>         return "CHAR(%(length)s)" % {'length' : self.length}
>
>     def get_dbapi_type(self, dbapi):
>         return dbapi.FIXED_CHAR
>
> from sqlalchemy.databases import oracle
> oracle.OracleChar = OracleChar
>
>
>
> > Here's how:
>
> > In [70]: query = "select * from price_sources where description
> > = :someparam"
> > In [71]: cursor.setinputsizes(someparam=cx_Oracle.FIXED_CHAR)
> > Out[71]: {'someparam': <cx_Oracle.FIXED_CHAR with value None>}
> > In [72]: cursor.execute(query, dict(someparam='EJV')).fetchall()
> > Out[72]:
> > [(149,
>
> > 'EJV
> > ')]
>
> > Any help would be appreciated.
>
> > On Sep 30, 2:36 pm, volx <victor.o... <at> gmail.com> wrote:
> >> I have just now and it looks that this post probably belongs on
> >> cx_Oracle mailing list.
>
> >> In [47]: cursor.execute("select * from price_sources where desciption
> >> = :someparam", dict(someparam='EJV')).fetchall()
> >> Out[47]: []
> >> In [49]: cursor.execute("select * from price_sources where desciption
> >> = :someparam", dict
> >> (someparam='EJV
> >> ')).fetchall()
> >> Out[49]:
> >> [(149,
>
> >> 'EJV
> >> ')]
>
> >> On Sep 30, 2:18 pm, Michael Bayer <mike... <at> zzzcomputing.com> wrote:
>
> >> > On Sep 30, 2:07 pm, volx <victor.o... <at> gmail.com> wrote:
>
> >> > > Hello all:
>
> >> > > Consider table:
>
> >> > > CREATE TABLE PRICE_SOURCES (
> >> > >    ID decimal(22) PRIMARY KEY NOT NULL,
> >> > >    DESCRIPTION char(100) NOT NULL
> >> > > )
>
> >> > > and this screen dump from ipython session:
>
> >> > > In [28]: import cx_Oracle
> >> > > In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
> >> > > In [30]: con = cx_Oracle.connect('myuser/mypass <at> mydb')
> >> > > In [31]: cursor = con.cursor()
> >> > > In [32]: cursor.execute("select * from price_sources where
> >> description
> >> > > = 'EJV'").fetchall()
> >> > > Out[32]:
> >> > > [(149,
>
> >> > > 'EJV
> >> > > ')]
>
> >> > > Note that the result has padding in the column "description" as it
> >> is
> >> > > of type CHAR(100), with is fixed length.
>
> >> > > Now, let's do the same in SqlAlchemy.
>
> >> > > In [33]: soup = SqlSoup('oracle://myuser:mypass <at> mydb')
> >> > > In [34]: soup.price_sources.filter_by(description='EJV').all()
> >> > > Out[34]: []
>
> >> > > As you can see it returns zero rows. Only after I pad the parameter
> >> > > for where clause will I get my row back:
>
> >> > > In [35]: soup.price_sources.filter_by
> >> > > (desciption='EJV
> >> > > ').all()
> >> > > Out[35]: [MappedPrice_sources(pricesource=Decimal
> >> > > ('149'),desciption='EJV
> >> > > ')]
>
> >> > > The same behavior happens even if I use ORM and regardless whether
> >> the
> >> > > column metadata is defined with String, OracleString or CHAR.
>
> >> > > I can understand the query result being padded as it is consistent
> >> > > with behavior everywhere but the where clasues must not. It is a big
> >> > > problem if you try to use session.merge() because you have to pad
> >> > > contents of object fields otherwise the ORM will insert a duplicate.
>
> >> > > Please help. I hope I am doing something wrong or missing something.
>
> >> > that's a little strange.  have you tried:
>
> >> > In [28]: import cx_Oracle
> >> > In [30]: con = cx_Oracle.connect('myuser/mypass <at> mydb')
> >> > In [31]: cursor = con.cursor()
> >> > In [32]: cursor.execute("select * from price_sources where description
> >> > = :someparam", dict(someparam='EJV')).fetchall()
>
> >> > ?
>
> >> > since the usage of bind parameters is how it actually runs.  If same
> >> > behavior there then it's a cx_oracle behavior.    you can verify this
> >> > by turning on SQL echoing.  As a workaround you can build yourself a
> >> > TypeDecorator that elongates/truncates incoming strings though that
> >> > seems a little inconvenient.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---


Gmane