date:20160609

Re: [sqlalchemy] utf8 error upon insert

2016-06-09 Thread Mike Bayer



VARBINARY should not have a utf-8 encoding step at all.  I can replace 
VARBINARY directly in my script and there's no problem; can you upgrade 
your pymysql?  I seem to recall someone having this problem recently.



Also please run the script below (with your database URL) and send the 
full output, as it will include things like the SQL MODE you're running 
and other things:


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(VARBINARY(255))

e = create_engine("mysql+pymysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

gcm_key = 
'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'


s = Session(e)
s.add(A(data=gcm_key))
s.commit()



On 06/09/2016 11:16 AM, Ven Karri wrote:

The only difference is that I am using sqlalchemy.types.VARBINARY(256)
instead of String(255)

On Thursday, June 9, 2016 at 10:47:32 AM UTC-4, Mike Bayer wrote:

what charset is in your my.cnf and/or how are you connecting.  Mine
only
produces a warning.  Here is an MCVE (definition: I can actually run
it):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
 __tablename__ = 'a'
 id = Column(Integer, primary_key=True)
 data = Column(String(255))

e = create_engine("mysql+pymysql://scott:tiger@localhost/test",
echo=True)
Base.metadata.create_all(e)

gcm_key =

'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'


s = Session(e)
s.add(A(data=gcm_key))
s.commit()




output:

python test.py
/home/classic/.venv/lib/python2.7/site-packages/pymysql/cursors.py:146:
Warning: Table 'test' already exists
   result = self._query(query)
/home/classic/.venv/lib/python2.7/site-packages/pymysql/cursors.py:146:
Warning: Incorrect string value: '\xFEE\x87\xE7\xC9\xE5...' for column
'data' at row 1
   result = self._query(query)


please run this script and modify it to show your error thanks!





On 06/09/2016 10:42 AM, Ven Karri wrote:
> Any ideas?
>
> On Thursday, June 9, 2016 at 10:21:27 AM UTC-4, Ven Karri wrote:
>
> Using, python 2.7 using 'mysql+pymysql' driver
>
> Code is very simple:
>
> gcm_key =
>

'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'

> model = Sample(gcm_key=gcm_key)
> session.add(model)
>
> Now what you said is to make it u'some string'. The string in
> question here is the gcm_key. So, I did this:
>
> gcm_key = gcm_key.decode('utf8')
>
> That throws an error:
>
> UnicodeDecodeError: 'utf8' codec can't decode byte 0xfe in
position
> 0: invalid start byte
>
> On Thursday, June 9, 2016 at 10:04:49 AM UTC-4, Mike Bayer wrote:
>
>
>
> On 06/09/2016 09:52 AM, Ven Karri wrote:
> > I am getting a UTF-8 error upon insert using sql alchemy
ORM.
> The same
> > query runs fine when I run using raw sql. Here's the ORM
query:
> >
> > rotating_keys_object = rotating_keys_model(
> > gcm_key=rot_gcm_key,
> > nonce=rot_nonce,
> > tag=rot_tag,
> > operational_team_id=self.operational_team_id
> > )
> > session.add(rotating_keys_object)
> >
> > Here's the error:
> >
> > DatabaseError: (raised as a result of Query-invoked
autoflush;
> consider
> > using a session.no_autoflush block if this flush is
occurring
> > prematurely) (DatabaseError) 1300: Invalid utf8
character string:
> > 'FE4587' u'INSERT INTO rotating_keys (gcm_key, nonce, tag,
> > operational_team_id) VALUES (%(gcm_key)s, %(nonce)s,
%(tag)s,
> > %(operational_team_id)s)' {'gcm_key':
> >
>

'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',

>
> > 'nonce':
'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N',
> > 'operational_team_id': 1, 'tag':
> > ";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"}
>
> when you deal with non-ascii values in Python, it's best
to use an
> encoding-neutral Python unicode object, in Py2K this is a
string
> like
> u'some

Re: [sqlalchemy] utf8 error upon insert

2016-06-09 Thread Ven Karri

The only difference is that I am using sqlalchemy.types.VARBINARY(256) 
instead of String(255)

On Thursday, June 9, 2016 at 10:47:32 AM UTC-4, Mike Bayer wrote:
>
> what charset is in your my.cnf and/or how are you connecting.  Mine only 
> produces a warning.  Here is an MCVE (definition: I can actually run it): 
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
>
> class A(Base): 
>  __tablename__ = 'a' 
>  id = Column(Integer, primary_key=True) 
>  data = Column(String(255)) 
>
> e = create_engine("mysql+pymysql://scott:tiger@localhost/test", echo=True) 
> Base.metadata.create_all(e) 
>
> gcm_key = 
> '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'
>  
>
>
> s = Session(e) 
> s.add(A(data=gcm_key)) 
> s.commit() 
>
>
>
>
> output: 
>
> python test.py 
> /home/classic/.venv/lib/python2.7/site-packages/pymysql/cursors.py:146: 
> Warning: Table 'test' already exists 
>result = self._query(query) 
> /home/classic/.venv/lib/python2.7/site-packages/pymysql/cursors.py:146: 
> Warning: Incorrect string value: '\xFEE\x87\xE7\xC9\xE5...' for column 
> 'data' at row 1 
>result = self._query(query) 
>
>
> please run this script and modify it to show your error thanks! 
>
>
>
>
>
> On 06/09/2016 10:42 AM, Ven Karri wrote: 
> > Any ideas? 
> > 
> > On Thursday, June 9, 2016 at 10:21:27 AM UTC-4, Ven Karri wrote: 
> > 
> > Using, python 2.7 using 'mysql+pymysql' driver 
> > 
> > Code is very simple: 
> > 
> > gcm_key = 
> > 
> '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'
>  
>
> > model = Sample(gcm_key=gcm_key) 
> > session.add(model) 
> > 
> > Now what you said is to make it u'some string'. The string in 
> > question here is the gcm_key. So, I did this: 
> > 
> > gcm_key = gcm_key.decode('utf8') 
> > 
> > That throws an error: 
> > 
> > UnicodeDecodeError: 'utf8' codec can't decode byte 0xfe in position 
> > 0: invalid start byte 
> > 
> > On Thursday, June 9, 2016 at 10:04:49 AM UTC-4, Mike Bayer wrote: 
> > 
> > 
> > 
> > On 06/09/2016 09:52 AM, Ven Karri wrote: 
> > > I am getting a UTF-8 error upon insert using sql alchemy ORM. 
> > The same 
> > > query runs fine when I run using raw sql. Here's the ORM 
> query: 
> > > 
> > > rotating_keys_object = rotating_keys_model( 
> > > gcm_key=rot_gcm_key, 
> > > nonce=rot_nonce, 
> > > tag=rot_tag, 
> > > operational_team_id=self.operational_team_id 
> > > ) 
> > > session.add(rotating_keys_object) 
> > > 
> > > Here's the error: 
> > > 
> > > DatabaseError: (raised as a result of Query-invoked autoflush; 
> > consider 
> > > using a session.no_autoflush block if this flush is occurring 
> > > prematurely) (DatabaseError) 1300: Invalid utf8 character 
> string: 
> > > 'FE4587' u'INSERT INTO rotating_keys (gcm_key, nonce, tag, 
> > > operational_team_id) VALUES (%(gcm_key)s, %(nonce)s, %(tag)s, 
> > > %(operational_team_id)s)' {'gcm_key': 
> > > 
> > 
> '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',
>  
>
> > 
> > > 'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N', 
> > > 'operational_team_id': 1, 'tag': 
> > > ";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"} 
> > 
> > when you deal with non-ascii values in Python, it's best to use 
> an 
> > encoding-neutral Python unicode object, in Py2K this is a string 
> > like 
> > u'some string'.   The conversion to utf8 is done by the database 
> > driver 
> > when it is passed from your application to the driver. 
> > 
> > If that's not the problem here then you'd need to illustrate 
> > many more 
> > details, including version of python in use, database driver in 
> > use, an 
> > MCVE code example that we can run (see 
> > http://stackoverflow.com/help/mcve 
> > ). 
> > 
> > 
> > 
> > 
> > > 
> > > -- 
> > > 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+...@googlegroups.com 
> > > . 
>
> > > To post to this group, send email to sqlal...@googlegroups.com 
> > > . 
> > > Visit this group at

Re: [sqlalchemy] utf8 error upon insert

2016-06-09 Thread Mike Bayer

what charset is in your my.cnf and/or how are you connecting.  Mine only 
produces a warning.  Here is an MCVE (definition: I can actually run it):


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String(255))

e = create_engine("mysql+pymysql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)

gcm_key = 
'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'


s = Session(e)
s.add(A(data=gcm_key))
s.commit()




output:

python test.py
/home/classic/.venv/lib/python2.7/site-packages/pymysql/cursors.py:146: 
Warning: Table 'test' already exists

  result = self._query(query)
/home/classic/.venv/lib/python2.7/site-packages/pymysql/cursors.py:146: 
Warning: Incorrect string value: '\xFEE\x87\xE7\xC9\xE5...' for column 
'data' at row 1

  result = self._query(query)


please run this script and modify it to show your error thanks!





On 06/09/2016 10:42 AM, Ven Karri wrote:

Any ideas?

On Thursday, June 9, 2016 at 10:21:27 AM UTC-4, Ven Karri wrote:

Using, python 2.7 using 'mysql+pymysql' driver

Code is very simple:

gcm_key =

'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'
model = Sample(gcm_key=gcm_key)
session.add(model)

Now what you said is to make it u'some string'. The string in
question here is the gcm_key. So, I did this:

gcm_key = gcm_key.decode('utf8')

That throws an error:

UnicodeDecodeError: 'utf8' codec can't decode byte 0xfe in position
0: invalid start byte

On Thursday, June 9, 2016 at 10:04:49 AM UTC-4, Mike Bayer wrote:



On 06/09/2016 09:52 AM, Ven Karri wrote:
> I am getting a UTF-8 error upon insert using sql alchemy ORM.
The same
> query runs fine when I run using raw sql. Here's the ORM query:
>
> rotating_keys_object = rotating_keys_model(
> gcm_key=rot_gcm_key,
> nonce=rot_nonce,
> tag=rot_tag,
> operational_team_id=self.operational_team_id
> )
> session.add(rotating_keys_object)
>
> Here's the error:
>
> DatabaseError: (raised as a result of Query-invoked autoflush;
consider
> using a session.no_autoflush block if this flush is occurring
> prematurely) (DatabaseError) 1300: Invalid utf8 character string:
> 'FE4587' u'INSERT INTO rotating_keys (gcm_key, nonce, tag,
> operational_team_id) VALUES (%(gcm_key)s, %(nonce)s, %(tag)s,
> %(operational_team_id)s)' {'gcm_key':
>

'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',

> 'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N',
> 'operational_team_id': 1, 'tag':
> ";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"}

when you deal with non-ascii values in Python, it's best to use an
encoding-neutral Python unicode object, in Py2K this is a string
like
u'some string'.   The conversion to utf8 is done by the database
driver
when it is passed from your application to the driver.

If that's not the problem here then you'd need to illustrate
many more
details, including version of python in use, database driver in
use, an
MCVE code example that we can run (see
http://stackoverflow.com/help/mcve
).




>
> --
> 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+...@googlegroups.com
> .
> To post to this group, send email to sqlal...@googlegroups.com
> .
> Visit this group at https://groups.google.com/group/sqlalchemy
.
> For more options, visit https://groups.google.com/d/optout
.

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] utf8 error upon insert

2016-06-09 Thread Mike Bayer




On 06/09/2016 10:21 AM, Ven Karri wrote:

Using, python 2.7 using 'mysql+pymysql' driver

Code is very simple:

gcm_key =
'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'
model = Sample(gcm_key=gcm_key)
session.add(model)

Now what you said is to make it u'some string'. The string in question
here is the gcm_key. So, I did this:

gcm_key = gcm_key.decode('utf8')

That throws an error:

UnicodeDecodeError: 'utf8' codec can't decode byte 0xfe in position 0:
invalid start byte


so the data you're starting out with is not valid utf8.  That would be 
your problem.







On Thursday, June 9, 2016 at 10:04:49 AM UTC-4, Mike Bayer wrote:



On 06/09/2016 09:52 AM, Ven Karri wrote:
> I am getting a UTF-8 error upon insert using sql alchemy ORM. The
same
> query runs fine when I run using raw sql. Here's the ORM query:
>
> rotating_keys_object = rotating_keys_model(
> gcm_key=rot_gcm_key,
> nonce=rot_nonce,
> tag=rot_tag,
> operational_team_id=self.operational_team_id
> )
> session.add(rotating_keys_object)
>
> Here's the error:
>
> DatabaseError: (raised as a result of Query-invoked autoflush;
consider
> using a session.no_autoflush block if this flush is occurring
> prematurely) (DatabaseError) 1300: Invalid utf8 character string:
> 'FE4587' u'INSERT INTO rotating_keys (gcm_key, nonce, tag,
> operational_team_id) VALUES (%(gcm_key)s, %(nonce)s, %(tag)s,
> %(operational_team_id)s)' {'gcm_key':
>

'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',

> 'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N',
> 'operational_team_id': 1, 'tag':
> ";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"}

when you deal with non-ascii values in Python, it's best to use an
encoding-neutral Python unicode object, in Py2K this is a string like
u'some string'.   The conversion to utf8 is done by the database driver
when it is passed from your application to the driver.

If that's not the problem here then you'd need to illustrate many more
details, including version of python in use, database driver in use, an
MCVE code example that we can run (see
http://stackoverflow.com/help/mcve
).




>
> --
> 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+...@googlegroups.com 
> .
> To post to this group, send email to sqlal...@googlegroups.com

> .
> Visit this group at https://groups.google.com/group/sqlalchemy
.
> For more options, visit https://groups.google.com/d/optout
.

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

2016-06-09 Thread Adrian

Yes, works fine with this change.

On Thursday, June 9, 2016 at 4:37:31 PM UTC+2, Mike Bayer wrote:
>
>
> if you can confirm the query is correct with this patch: 
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] utf8 error upon insert

2016-06-09 Thread Ven Karri

Any ideas?

On Thursday, June 9, 2016 at 10:21:27 AM UTC-4, Ven Karri wrote:
>
> Using, python 2.7 using 'mysql+pymysql' driver
>
> Code is very simple:
>
> gcm_key = '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\
> xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'
> model = Sample(gcm_key=gcm_key)
> session.add(model)
>
> Now what you said is to make it u'some string'. The string in question 
> here is the gcm_key. So, I did this:
>
> gcm_key = gcm_key.decode('utf8')
>
> That throws an error:
>
> UnicodeDecodeError: 'utf8' codec can't decode byte 0xfe in position 0: 
> invalid start byte
>
> On Thursday, June 9, 2016 at 10:04:49 AM UTC-4, Mike Bayer wrote:
>>
>>
>>
>> On 06/09/2016 09:52 AM, Ven Karri wrote: 
>> > I am getting a UTF-8 error upon insert using sql alchemy ORM. The same 
>> > query runs fine when I run using raw sql. Here's the ORM query: 
>> > 
>> > rotating_keys_object = rotating_keys_model( 
>> > gcm_key=rot_gcm_key, 
>> > nonce=rot_nonce, 
>> > tag=rot_tag, 
>> > operational_team_id=self.operational_team_id 
>> > ) 
>> > session.add(rotating_keys_object) 
>> > 
>> > Here's the error: 
>> > 
>> > DatabaseError: (raised as a result of Query-invoked autoflush; consider 
>> > using a session.no_autoflush block if this flush is occurring 
>> > prematurely) (DatabaseError) 1300: Invalid utf8 character string: 
>> > 'FE4587' u'INSERT INTO rotating_keys (gcm_key, nonce, tag, 
>> > operational_team_id) VALUES (%(gcm_key)s, %(nonce)s, %(tag)s, 
>> > %(operational_team_id)s)' {'gcm_key': 
>> > 
>> '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',
>>  
>>
>> > 'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N', 
>> > 'operational_team_id': 1, 'tag': 
>> > ";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"} 
>>
>> when you deal with non-ascii values in Python, it's best to use an 
>> encoding-neutral Python unicode object, in Py2K this is a string like 
>> u'some string'.   The conversion to utf8 is done by the database driver 
>> when it is passed from your application to the driver. 
>>
>> If that's not the problem here then you'd need to illustrate many more 
>> details, including version of python in use, database driver in use, an 
>> MCVE code example that we can run (see http://stackoverflow.com/help/mcve). 
>>
>>
>>
>>
>>
>> > 
>> > -- 
>> > 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+...@googlegroups.com 
>> > . 
>> > To post to this group, send email to sqlal...@googlegroups.com 
>> > . 
>> > Visit this group at https://groups.google.com/group/sqlalchemy. 
>> > For more options, visit https://groups.google.com/d/optout. 
>>
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

2016-06-09 Thread Mike Bayer

the deannotation step is making a clone of the CTE and not maintaining 
all the linkages properly.   Likely a bug but a really deep one I could 
use some help on.


if you can confirm the query is correct with this patch:

diff --git a/lib/sqlalchemy/orm/properties.py 
b/lib/sqlalchemy/orm/properties.py

index f3dce75..45b9e83 100644
--- a/lib/sqlalchemy/orm/properties.py
+++ b/lib/sqlalchemy/orm/properties.py
@@ -118,8 +118,9 @@ class ColumnProperty(StrategizedProperty):
 """
 super(ColumnProperty, self).__init__()
 self._orig_columns = [expression._labeled(c) for c in columns]
-self.columns = [expression._labeled(_orm_full_deannotate(c))
-for c in columns]
+#self.columns = [expression._labeled(_orm_full_deannotate(c))
+#for c in columns]
+self.columns = [expression._labeled(c) for c in columns]
 self.group = kwargs.pop('group', None)
 self.deferred = kwargs.pop('deferred', False)
 self.instrument = kwargs.pop('_instrument', True)

then we know it's strictly the visit-clone step.   isolated test case 
would need to be constructed, the style we're looking for would be along 
the lines of what we see in 
https://bitbucket.org/zzzeek/sqlalchemy/src/31a0da32a8af2503c6b94123a0e869816d83c707/test/sql/test_generative.py?at=master=file-view-default#test_generative.py-299 
though perhaps the CTE specific tests would be in test/sql/test_cte.py.




On 06/09/2016 10:11 AM, Adrian wrote:

I've already tried not specifying a name - in that case it's `anon_2` in
the error.

Here's an MCVE:
https://gist.github.com/ThiefMaster/593e5a78f08d6323eb1b88270256baa7

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] utf8 error upon insert

2016-06-09 Thread Ven Karri

Using, python 2.7 using 'mysql+pymysql' driver

Code is very simple:

gcm_key = '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\
xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f'
model = Sample(gcm_key=gcm_key)
session.add(model)

Now what you said is to make it u'some string'. The string in question here 
is the gcm_key. So, I did this:

gcm_key = gcm_key.decode('utf8')

That throws an error:

UnicodeDecodeError: 'utf8' codec can't decode byte 0xfe in position 0: 
invalid start byte

On Thursday, June 9, 2016 at 10:04:49 AM UTC-4, Mike Bayer wrote:
>
>
>
> On 06/09/2016 09:52 AM, Ven Karri wrote: 
> > I am getting a UTF-8 error upon insert using sql alchemy ORM. The same 
> > query runs fine when I run using raw sql. Here's the ORM query: 
> > 
> > rotating_keys_object = rotating_keys_model( 
> > gcm_key=rot_gcm_key, 
> > nonce=rot_nonce, 
> > tag=rot_tag, 
> > operational_team_id=self.operational_team_id 
> > ) 
> > session.add(rotating_keys_object) 
> > 
> > Here's the error: 
> > 
> > DatabaseError: (raised as a result of Query-invoked autoflush; consider 
> > using a session.no_autoflush block if this flush is occurring 
> > prematurely) (DatabaseError) 1300: Invalid utf8 character string: 
> > 'FE4587' u'INSERT INTO rotating_keys (gcm_key, nonce, tag, 
> > operational_team_id) VALUES (%(gcm_key)s, %(nonce)s, %(tag)s, 
> > %(operational_team_id)s)' {'gcm_key': 
> > 
> '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',
>  
>
> > 'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N', 
> > 'operational_team_id': 1, 'tag': 
> > ";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"} 
>
> when you deal with non-ascii values in Python, it's best to use an 
> encoding-neutral Python unicode object, in Py2K this is a string like 
> u'some string'.   The conversion to utf8 is done by the database driver 
> when it is passed from your application to the driver. 
>
> If that's not the problem here then you'd need to illustrate many more 
> details, including version of python in use, database driver in use, an 
> MCVE code example that we can run (see http://stackoverflow.com/help/mcve). 
>
>
>
>
>
> > 
> > -- 
> > 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+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

2016-06-09 Thread Adrian

I've already tried not specifying a name - in that case it's `anon_2` in 
the error.

Here's an MCVE: 
https://gist.github.com/ThiefMaster/593e5a78f08d6323eb1b88270256baa7

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] utf8 error upon insert

2016-06-09 Thread Mike Bayer

On 06/09/2016 09:52 AM, Ven Karri wrote:

I am getting a UTF-8 error upon insert using sql alchemy ORM. The same
query runs fine when I run using raw sql. Here's the ORM query:

rotating_keys_object = rotating_keys_model(
gcm_key=rot_gcm_key,
nonce=rot_nonce,
tag=rot_tag,
operational_team_id=self.operational_team_id
)
session.add(rotating_keys_object)

Here's the error:

DatabaseError: (raised as a result of Query-invoked autoflush; consider
using a session.no_autoflush block if this flush is occurring
prematurely) (DatabaseError) 1300: Invalid utf8 character string:
'FE4587' u'INSERT INTO rotating_keys (gcm_key, nonce, tag,
operational_team_id) VALUES (%(gcm_key)s, %(nonce)s, %(tag)s,
%(operational_team_id)s)' {'gcm_key':
'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',
'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N',
'operational_team_id': 1, 'tag':
";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"}

when you deal with non-ascii values in Python, it's best to use an
encoding-neutral Python unicode object, in Py2K this is a string like
u'some string'. The conversion to utf8 is done by the database driver
when it is passed from your application to the driver.

If that's not the problem here then you'd need to illustrate many more
details, including version of python in use, database driver in use, an
MCVE code example that we can run (see http://stackoverflow.com/help/mcve).

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

2016-06-09 Thread Mike Bayer




On 06/09/2016 05:39 AM, Adrian wrote:

I'm trying to add a `deep_children_count` column property to one of my
models.
As a regular property it works perfectly fine but I'd like to make it a
column property so I don't have to spam extra queries if I need the
counts for multiple objects.

So I tried this:

|
cat_alias =db.aliased(Category)
cte_query
=(select([cat_alias.id,db.cast(array([]),ARRAY(db.Integer)).label('parents')])
 .where(cat_alias.parent_id.is_(None)&~cat_alias.is_deleted)
 .cte('chains',recursive=True))
parent_query
=(select([cat_alias.id,cte_query.c.parents.op('||')(cat_alias.parent_id)])
.where((cat_alias.parent_id
==cte_query.c.id)&~cat_alias.is_deleted))
cte_query =cte_query.union_all(parent_query)
query
=select([db.func.count()]).where(cte_query.c.parents.contains(array([Category.id])))
Category.deep_children_count =column_property(query)
|


Unfortunately this fails with an exception when loading one of the objects:
CompileError: Multiple, unrelated CTEs found with the same name: u'chains'

I'm not sure why I end up with *multiple* CTEs and since it's a compile
error I cannot look at the SQL it
tried to generate either...


"name" is optional, it will generate an anoymous name if omitted.I'm 
not sure what the problem is here but perhaps it will get a little 
further that way and you'd be able to see what the problem is.


otherwise I'd need an MCVE for this in order to play with it.   CTEs are 
painful.






--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: Tagging an Alembic revision

2016-06-09 Thread Mike Bayer




On 06/09/2016 05:11 AM, Søren Løvborg wrote:

On Wed, Jun 8, 2016 at 4:32 PM, Mike Bayer > wrote:

Branch labels are exactly what solves this?What's wrong with
using a branch label?  you put "v1.2.1" as a branch label in the
target revision and then your "alembic downgrade v1.2.1" command
works exactly.


Sorry, reading the documentation I was distracted by the @head notations
etc. and missed the part where a "bare" branch label does indeed work
like a tag.

From the docs, it still seems like branch labels were designed to solve
a different problem, and just incidentally happens to solve this too,
but if you say it's a proper use of branch_labels, I'm obviously not
gonna argue. ;-)


That is all true, it was designed for the branching issue and not as 
much "tag this revision", though these concepts are always very similar 
in version control systems.





There's a weird side-effect, in that our Alembic history is (so far)
linear, so all branch labels show up for every revision, e.g.:

Branch names: v1.2.3, v1.2.2, v1.2.1, v1.2.0

That was part of what confused me, and could get unwieldy eventually,
but I guess it's just a cosmetic issue.



OK, then that is actually an argument to add a new "tags" feature as 
well that is pretty much like a branch name but doesn't get associated 
with other revisions in the history view.




Yeah, but we can't depend on people having full VCS history available,
we have to support snapshot downloads too. :-/

So branch_labels it is. Thanks!


Yeah.  But yeah, as I mentioned in my other email having a "tags" 
collection next to "branch_labels" is OK for me as well with some 
different semantics in alembic history etc. (any other effects you can 
think of?)   Basically you're alpha testing this :).






Best,
Søren

--
You received this message because you are subscribed to the Google
Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy-alembic+unsubscr...@googlegroups.com
.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: utf8 error upon insert

2016-06-09 Thread Ven Karri

Btw, It's a MySQL backend

On Thursday, June 9, 2016 at 9:52:41 AM UTC-4, Ven Karri wrote:
>
> I am getting a UTF-8 error upon insert using sql alchemy ORM. The same 
> query runs fine when I run using raw sql. Here's the ORM query:
>
> rotating_keys_object = rotating_keys_model(
> gcm_key=rot_gcm_key,
> nonce=rot_nonce,
> tag=rot_tag,
> operational_team_id=self.operational_team_id
> )
> session.add(rotating_keys_object)
>
> Here's the error:
>
> DatabaseError: (raised as a result of Query-invoked autoflush; consider 
> using a session.no_autoflush block if this flush is occurring prematurely) 
> (DatabaseError) 1300: Invalid utf8 character string: 'FE4587' u'INSERT INTO 
> rotating_keys (gcm_key, nonce, tag, operational_team_id) VALUES 
> (%(gcm_key)s, %(nonce)s, %(tag)s, %(operational_team_id)s)' {'gcm_key': 
> '\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',
>  
> 'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N', 
> 'operational_team_id': 1, 'tag': 
> ";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"}
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] utf8 error upon insert

2016-06-09 Thread Ven Karri

I am getting a UTF-8 error upon insert using sql alchemy ORM. The same 
query runs fine when I run using raw sql. Here's the ORM query:

rotating_keys_object = rotating_keys_model(
gcm_key=rot_gcm_key,
nonce=rot_nonce,
tag=rot_tag,
operational_team_id=self.operational_team_id
)
session.add(rotating_keys_object)

Here's the error:

DatabaseError: (raised as a result of Query-invoked autoflush; consider 
using a session.no_autoflush block if this flush is occurring prematurely) 
(DatabaseError) 1300: Invalid utf8 character string: 'FE4587' u'INSERT INTO 
rotating_keys (gcm_key, nonce, tag, operational_team_id) VALUES 
(%(gcm_key)s, %(nonce)s, %(tag)s, %(operational_team_id)s)' {'gcm_key': 
'\xfeE\x87\xe7\xc9\xe5\xec\xe0\x9c\xd6\x85\x11\xc7\xebd\xe3\x7f\xd9\xfel\xe6\x86"j\xbe=\xf4\xd7\x95\x99F\x8f',
 
'nonce': 'o\xcb\x06\xe0\xe9\xech\xed\xed?T\xf4\xaf\x9a\xe1N', 
'operational_team_id': 1, 'tag': 
";p\xcce\xd2\xb8'\xf5\x89q\xc1\xa0\xfa\xff\x11\xf9"}

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

2016-06-09 Thread Adrian

I'm trying to add a `deep_children_count` column property to one of my 
models.
As a regular property it works perfectly fine but I'd like to make it a 
column property so I don't have to spam extra queries if I need the counts 
for multiple objects.

So I tried this:

cat_alias = db.aliased(Category)
cte_query = (select([cat_alias.id, db.cast(array([]), ARRAY(db.Integer
)).label('parents')])
 .where(cat_alias.parent_id.is_(None) & ~cat_alias.
is_deleted)
 .cte('chains', recursive=True))
parent_query = (select([cat_alias.id, cte_query.c.parents.op('||')(
cat_alias.parent_id)])
.where((cat_alias.parent_id == cte_query.c.id) & ~
cat_alias.is_deleted))
cte_query = cte_query.union_all(parent_query)
query = select([db.func.count()]).where(cte_query.c.parents.contains(
array([Category.id])))
Category.deep_children_count = column_property(query)


Unfortunately this fails with an exception when loading one of the objects:
CompileError: Multiple, unrelated CTEs found with the same name: u'chains'

I'm not sure why I end up with *multiple* CTEs and since it's a compile 
error I cannot look at the SQL it
tried to generate either...

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: Tagging an Alembic revision

2016-06-09 Thread Søren Løvborg

On Wed, Jun 8, 2016 at 4:32 PM, Mike Bayer  wrote:

> Branch labels are exactly what solves this?What's wrong with using a
> branch label?  you put "v1.2.1" as a branch label in the target revision
> and then your "alembic downgrade v1.2.1" command works exactly.
>

Sorry, reading the documentation I was distracted by the @head notations
etc. and missed the part where a "bare" branch label does indeed work like
a tag.

>From the docs, it still seems like branch labels were designed to solve a
different problem, and just incidentally happens to solve this too, but if
you say it's a proper use of branch_labels, I'm obviously not gonna argue.
;-)

There's a weird side-effect, in that our Alembic history is (so far)
linear, so all branch labels show up for every revision, e.g.:

Branch names: v1.2.3, v1.2.2, v1.2.1, v1.2.0

That was part of what confused me, and could get unwieldy eventually, but I
guess it's just a cosmetic issue.

of course, if you actually git tag your project, the head revision file can
> be located from that git tag.

Yeah, but we can't depend on people having full VCS history available, we
have to support snapshot downloads too. :-/

So branch_labels it is. Thanks!

Best,
Søren

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] utf8 error upon insert

Re: [sqlalchemy] utf8 error upon insert

Re: [sqlalchemy] utf8 error upon insert

Re: [sqlalchemy] utf8 error upon insert

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

Re: [sqlalchemy] utf8 error upon insert

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

Re: [sqlalchemy] utf8 error upon insert

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

Re: [sqlalchemy] utf8 error upon insert

Re: [sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

Re: Tagging an Alembic revision

[sqlalchemy] Re: utf8 error upon insert

[sqlalchemy] utf8 error upon insert

[sqlalchemy] "CompileError: Multiple, unrelated CTEs found with the same name" when using column property

Re: Tagging an Alembic revision

16 matches

Site Navigation

Mail list logo

Footer information