Re: [sqlalchemy] Re: Bound parameter on Postgres/psycopg2 Range data types
Actually, looks like the problem is with psycopg2's handling of range
types, specifically with integers. Test attached. Will forward to psycopg2
maintainers.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import (
INT4RANGE,
NUMRANGE
)
import psycopg2
from psycopg2.extras import NumericRange
# Sqlalchemy db interactions
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
intrange = Column(INT4RANGE)
numrange = Column(NUMRANGE)
e = create_engine("postgresql://brian@10.0.1.10:5432/app", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
# Insert via string type
foo_one = Foo(id=1, intrange='[1, 10]', numrange='[1.0, 10.0]')
# Insert via NumericRange type
foo_two = Foo(id=2, intrange=NumericRange(lower=1, upper=10, bounds='[]'), numrange=NumericRange(lower=1.0, upper=10.0, bounds='[]'))
sess.add_all([foo_one, foo_two])
sess.commit()
foo_one = sess.query(Foo).filter_by(id=1).first()
foo_two = sess.query(Foo).filter_by(id=2).first()
# Psycopg2 db interactions
conn = psycopg2.connect('dbname=app user=brian')
cur = conn.cursor()
foo_three = Foo(id=3, intrange=NumericRange(lower=1, upper=10, bounds='[]'), numrange=NumericRange(lower=1.0, upper=10.0, bounds='[]'))
cur.execute("INSERT INTO foo (id, intrange, numrange) VALUES (%s, %s, %s)", ((foo_three.id,), (foo_three.intrange,), (foo_three.numrange,)))
conn.commit()
cur.execute('SELECT intrange FROM foo WHERE id=3;')
foo_three.intrange = cur.fetchone()
cur.execute('SELECT numrange FROM foo WHERE id=3;')
foo_three.numrange = cur.fetchone()
cur.close()
conn.close()
# Tests
# These pass: string and NumericRange types the same when committed
assert foo_one.intrange == foo_two.intrange
assert foo_one.numrange == foo_two.numrange
# These pass: Sqlalchemy and psycopg2 the same
assert foo_three.intrange == (foo_one.intrange,)
assert foo_three.numrange == (foo_one.numrange,)
# These pass, but should not?
assert foo_one.intrange == NumericRange(lower=1, upper=11, bounds='[)')
assert foo_two.intrange == NumericRange(lower=1, upper=11, bounds='[)')
assert foo_three.intrange == (NumericRange(lower=1, upper=11, bounds='[)'),)
# These pass: numeric types work fine
assert foo_one.numrange == NumericRange(lower=1.0, upper=10.0, bounds='[]')
assert foo_two.numrange == NumericRange(lower=1.0, upper=10.0, bounds='[]')
assert foo_three.numrange == (NumericRange(lower=1.0, upper=10.0, bounds='[]'),)
# These fail, but should pass?
assert foo_one.intrange == NumericRange(lower=1, upper=10, bounds='[]')
assert foo_two.intrange == NumericRange(lower=1, upper=10, bounds='[]')
assert foo_three.intrange == (NumericRange(lower=1, upper=10, bounds='[]'),)
Re: [sqlalchemy] problems with mysql reflect
On 7/10/14, 3:49 PM, Paul Molodowitch wrote: > Whoops! Just noticed this was the totally wrong traceback! > > Here's the correct trace: > > Traceback (most recent call last): > File "", line 1, in > File "test.py", line 155, in > metadata.reflect(db.engine, only=tables) > File "./sqlalchemy/sql/schema.py", line 3277, in reflect > Table(name, self, **reflect_opts) > File "./sqlalchemy/sql/schema.py", line 352, in __new__ > table._init(name, metadata, *args, **kw) > File "./sqlalchemy/sql/schema.py", line 425, in _init > self._autoload(metadata, autoload_with, include_columns) > File "./sqlalchemy/sql/schema.py", line 437, in _autoload > self, include_columns, exclude_columns > File "./sqlalchemy/engine/base.py", line 1198, in run_callable > return callable_(self, *args, **kwargs) > File "./sqlalchemy/engine/default.py", line 355, in reflecttable > return insp.reflecttable(table, include_columns, exclude_columns) > File "./sqlalchemy/engine/reflection.py", line 463, in reflecttable > for col_d in self.get_columns(table_name, schema, > **table.dialect_kwargs): > TypeError: get_columns() keywords must be strings > with metadata.reflect(), OK. Can you please make a very short and self-contained test case and post a bug report? thanks. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Bound parameter on Postgres/psycopg2 Range data types
On 7/10/14, 2:46 PM, Brian Findlay wrote:
> Sqlalchemy seems to be coercing the upper boundary to be exclusive.
> See below tests (will need to change postgres db if you want to run them).
>
>
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.dialects.postgresql import INT4RANGE
> from psycopg2.extras import NumericRange
>
> Base = declarative_base()
>
> class Foo(Base):
> __tablename__ = 'foo'
> id = Column(Integer, primary_key=True)
> range = Column(INT4RANGE)
>
>
> e = create_engine("postgresql://brian@10.0.1.10:5432/test", echo=True)
> Base.metadata.create_all(e)
>
> sess = Session(e)
>
> foo_one = Foo(id=1, range='[1, 10]')
> foo_two = Foo(id=2, range=NumericRange(lower=1, upper=10, bounds='[]'))
> sess.add_all([foo_one, foo_two])
> sess.commit()
>
> #foo_one = sess.query(Foo).filter_by(id=1).first()
> #foo_two = sess.query(Foo).filter_by(id=2).first()
>
> # These pass
> assert foo_one.range == foo_two.range
> assert foo_one.range.lower == foo_two.range.lower
> assert foo_one.range.upper == foo_two.range.upper
>
> # These fail
> assert foo_one.range == NumericRange(lower=1, upper=10, bounds='[]')
> assert foo_two.range == NumericRange(lower=1, upper=10, bounds='[]')
>
> # But this passes
> assert foo_two.range == NumericRange(lower=1, upper=11, bounds='[)')
I don't do the range types, if you create a bug report, chris withers
can be assigned and he'll get a note about it. Or figure out a PR for us.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] problems with mysql reflect
Whoops! Just noticed this was the totally wrong traceback! Here's the correct trace: Traceback (most recent call last): File "", line 1, in File "test.py", line 155, in metadata.reflect(db.engine, only=tables) File "./sqlalchemy/sql/schema.py", line 3277, in reflect Table(name, self, **reflect_opts) File "./sqlalchemy/sql/schema.py", line 352, in __new__ table._init(name, metadata, *args, **kw) File "./sqlalchemy/sql/schema.py", line 425, in _init self._autoload(metadata, autoload_with, include_columns) File "./sqlalchemy/sql/schema.py", line 437, in _autoload self, include_columns, exclude_columns File "./sqlalchemy/engine/base.py", line 1198, in run_callable return callable_(self, *args, **kwargs) File "./sqlalchemy/engine/default.py", line 355, in reflecttable return insp.reflecttable(table, include_columns, exclude_columns) File "./sqlalchemy/engine/reflection.py", line 463, in reflecttable for col_d in self.get_columns(table_name, schema, **table.dialect_kwargs): TypeError: get_columns() keywords must be strings -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Bound parameter on Postgres/psycopg2 Range data types
Sqlalchemy seems to be coercing the upper boundary to be exclusive. See
below tests (will need to change postgres db if you want to run them).
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import INT4RANGE
from psycopg2.extras import NumericRange
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
range = Column(INT4RANGE)
e = create_engine("postgresql://brian@10.0.1.10:5432/test", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
foo_one = Foo(id=1, range='[1, 10]')
foo_two = Foo(id=2, range=NumericRange(lower=1, upper=10, bounds='[]'))
sess.add_all([foo_one, foo_two])
sess.commit()
#foo_one = sess.query(Foo).filter_by(id=1).first()
#foo_two = sess.query(Foo).filter_by(id=2).first()
# These pass
assert foo_one.range == foo_two.range
assert foo_one.range.lower == foo_two.range.lower
assert foo_one.range.upper == foo_two.range.upper
# These fail
assert foo_one.range == NumericRange(lower=1, upper=10, bounds='[]')
assert foo_two.range == NumericRange(lower=1, upper=10, bounds='[]')
# But this passes
assert foo_two.range == NumericRange(lower=1, upper=11, bounds='[)')
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Bound parameter on Postgres/psycopg2 Range data types
With the following model, I can currently set postgres range data types in
2 ways:
*Model:*
from sqlalchemy.dialects.postgresql import INT4RANGE
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
bar = Column(INT4RANGE)
*Method #1 - as string data type:*
foo.bar = '[{lower},{upper}]'.format(min=baz, max=qux)
*Results in the following being committed:*
INFO [sqlalchemy.engine.base.Engine][Dummy-3] {'bar': *'[1, 10]'*,
'foo_id': 1}
With the corresponding range being *'[1, 10]'*, as desired.
*Method #2 - as NumericRange data type:*
from psycopg2.extras import NumericRange
foo.bar = NumericRange(lower=baz, upper=qux, bounds='[]')
*Results in the following being committed:*
INFO [sqlalchemy.engine.base.Engine][Dummy-4] {'bar': *NumericRange(1, 10,
'[]')*, 'foo_id': 1}
With the corresponding range being *'[1, 11)'*.
The string method works as expected, but if I use it to set a range in the
controller (say, from a form submission) and then return that range to the
template engine, the `lower` and `upper` methods don't work because it's
currently stored as a string type instead of a NumericRange type.
Is there a better way to do this (while still using the Range data types)?
Thanks.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: same code same database but the not same insert result
On Wednesday, July 9, 2014 9:56:14 PM UTC-4, Frank Liou wrote:
>
> why get not the same result?
>
There is no reason why the example you gave would insert that value.
You need to make a standalone script that shows this error happening in
SqlAlchemy.
If you can't reproduce the error, then no one can help you.
Look at this test script I made for your example:
• https://gist.github.com/jvanasco/2ff0b9a31ac45198a060
It generates this SQL transaction, and shows everything working as-expected
:
2014-07-10 11:20:29,707 INFO sqlalchemy.engine.base.Engine COMMIT
2014-07-10 11:20:29,707 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-07-10 11:20:29,708 INFO sqlalchemy.engine.base.Engine INSERT INTO
friends(name) VALUES(?)
2014-07-10 11:20:29,708 INFO sqlalchemy.engine.base.Engine ('MTIzNDU2Nw==',)
2014-07-10 11:20:29,708 INFO sqlalchemy.engine.base.Engine ROLLBACK
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
