Re: [sqlalchemy] almbic postgres text_pattern_ops on index

[Mike Bayer]

On Thu, Jan 18, 2018 at 1:35 PM, kris  wrote:
>
>
> Try to create a postgres specific option while creating index , pointer
> appreciated
>
> this link
> http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.create_index
> suggest I can pass dialect specific options
>
> def upgrade():
> op.drop_index('ix_taggable_resource_value', 'taggable')
> op.create_index('ix_taggable_resource_value', 'taggable', [
> 'resource_value' ],
> postgresql_ops = { 'resource_value', 'text_pattern_ops'
> } )

postgresql_ops should be a dictionary, not a set

>
>
> NFO  [alembic.migration] Running upgrade 2cbd9f4d11af -> 318381ec60ba,
> indexes 4 deletes
> Traceback (most recent call last):
>   File "/home/kgk/Envs/bq059/bin/alembic", line 11, in 
> sys.exit(main())
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/config.py",
> line 298, in main
> CommandLine(prog=prog).main(argv=argv)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/config.py",
> line 293, in main
> self.run_cmd(cfg, options)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/config.py",
> line 279, in run_cmd
> **dict((k, getattr(options, k)) for k in kwarg)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/command.py",
> line 124, in upgrade
> script.run_env()
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/script.py",
> line 199, in run_env
> util.load_python_file(self.dir, 'env.py')
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/util.py",
> line 205, in load_python_file
> module = load_module_py(module_id, path)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/compat.py",
> line 58, in load_module_py
> mod = imp.load_source(module_id, path, fp)
>   File "./migrations/env.py", line 76, in 
> run_migrations_online()
>   File "./migrations/env.py", line 69, in run_migrations_online
> context.run_migrations()
>   File "", line 7, in run_migrations
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/environment.py",
> line 681, in run_migrations
> self.get_context().run_migrations(**kw)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/migration.py",
> line 225, in run_migrations
> change(**kw)
>   File "./migrations/versions/318381ec60ba_indexes_4_deletes.py", line 22,
> in upgrade
> postgresql_ops = { 'resource_value', 'text_pattern_ops' } )
>   File "", line 7, in create_index
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/operations.py",
> line 698, in create_index
> self._index(name, table_name, columns, schema=schema, **kw)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/ddl/impl.py",
> line 161, in create_index
> self._exec(schema.CreateIndex(index))
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/ddl/impl.py",
> line 76, in _exec
> conn.execute(construct, *multiparams, **params)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
> line 945, in execute
> return meth(self, multiparams, params)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
> line 68, in _execute_on_connection
> return connection._execute_ddl(self, multiparams, params)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
> line 996, in _execute_ddl
> if not self.schema_for_object.is_default else None)
>   File "", line 1, in 
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
> line 436, in compile
> return self._compiler(dialect, bind=bind, **kw)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
> line 26, in _compiler
> return dialect.ddl_compiler(dialect, self, **kw)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
> line 216, in __init__
> self.string = self.process(self.statement, **compile_kwargs)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
> line 242, in process
> return obj._compiler_dispatch(self, **kwargs)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
> line 81, in _compiler_dispatch
> return meth(self, **kw)
>   File
> "/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py",
> line 1670, in visit_create_index
> for expr in index.expressions
> TypeError: 'set' object has no attribute '__getitem__'
> An Unknown exception occured There was a problem initializing the Database
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, 

Re: [sqlalchemy] Suppress SQL statement in DBAPI Error Message?

[Mike Bayer]

On Thu, Jan 18, 2018 at 2:05 PM, Rick Suggs  wrote:
> Is there any way to suppress the SQL statement that is included in the DBAPI
> error message?

you can use the handle_error event:

http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=handle_error#sqlalchemy.events.ConnectionEvents.handle_error

on the exception object itself, you should be able to set
.statement="" and .params=None



>
> For example:
>
> sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for
> integer: "hello"
> LINE 1: INSERT INTO test (num, data) VALUES (100, 'hello');
>   ^
>  [SQL: "INSERT INTO test (num, data) VALUES (100, 'hello');"]
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] session.expire{,_all}_unmodified for dropping objects that are unchanged?!

[Torsten Landschoff]

Hello Mike,

first, thanks for considering my ramblings and thanks for the reply!

On 17.01.2018 23:23, Mike Bayer wrote:
> this is overkill. Here's what your code looks like without an ORM: 
Yes, I think my message was lost in translation or rather in
oversimplification.
> this is very specific to the very quirky and odd thing I'm doing in
> the first place, which is that I'm creating an endlessly long linked
> list - I detach the element.
The actual code does not create an endlessly long linked list of course,
it's more like a directed acyclic graph.
Nodes may have many outgoing edges so it's not so simple to know where
to cut :-)
>> This made me think about our usage of "session.expire": Mostly, we use it to
>> tell SQLAlchemy that this is a good time to forget this object in the
>> expectation that there are no pending changes. I found 13 calls to
>> session.expire in our code base.
> The way that loop is structured, I would not want to expire all
> attributes that have been flushed.   When you make a new Change(), it
> has a predecessor for which it needs to locate its primary key in
> order to assemble it into the foreign key column of the successor.  If
> you've expired that Change() object completely, you are forcing the
> ORM to un-expire the object using another SELECT to ensure it hasn't
> been DELETEd, so that it can use the Change.id value.  The more

True. And I admit that it did not think about cutting the links, I just
wanted to drop objects (and all related links) that are no longer needed.
I actually prefetch the primary keys of all objects touched or referred
to by a batch of incoming objects. After processing the batch, I drop
all objects not referred to by the next incoming batch and load those
required for processing the next batch.

> you can tell if an object is part of that "modified" collection using
> instance_state(obj).modified - if that's false, it's definitely clean.
> _modified isn't public.  You can also look inside of session.dirty.
Thanks for the pointer, I actually looked at the implementation of
session.dirty to find the internal _modified.
I replaced this with instance-state(obj).modified.
> ".new" or ".deleted").But the most in-depth method,
> session.is_modified(), does that state.modified check first before
> digging in, so since you can likely tolerate false positives you can
> stick with that.
False positives are not an issue, if an instance is kept for one extra
batch if will most likely be dropped in the next one.
> it's "safe" but as before if the object is involved in other objects
> still to be flushed, a new SELECT will need to be emitted to unexpire
> the object.
I droped safe_expire_all, it was only for completeness.
About the extra select for unexpiring: I will gladly take that
performance hit if I manage to reintroduce that bug.

Calling expire() for an instance that had pending updates was
(obviously) a bug in my application. I just wanted to go one step
further than just fixing the spot where it appeared and make it harder
to go there again.
> the case you illustrate is surprising and unusual. I would mark it
> up to a specific case you have here where you happen to be building up
> an endlessly long chain of objects for which the issue of memory is
> most cleanly handled by an explicit periodic break of the chain.
The initial code was better here because session.commit() would have
done that for us because of the implicit expire_all directly after the
flush.
As you noted above this lead to a number of extra selects to unexpire
all objects still needed.

BTW: Our application is a rich client where the user interface also
keeps mapped objects loaded in a ORM session, since reloading is quite
expensive. Updates are done mostly in worker threads and processes and
broadcast to all database sessions. For updating the display, all
modified objects are expired and refreshed from the database.

This is where I actually messed up and refreshed from the DB inside a
running transaction - before flushing the changes entered by the user. m(
Finding the cause made me think of other misuses of expiration.
>> Note that the documentation does not warn about dropping pending changes due 
>> to calls to expire.
> it's mentioned here:
> http://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#session-expire
> but just not in any kind of boldface:
Oops, sorry, I only checked the reference documentation.
> I actually probably wouldn't make expire() do what it does today, e.g.
> expire un-flushed changes.   When expire() was first created it was
> likely not possible or very easy to hit only DB-persisted attributes.
>  The attribute history system was rewritten several times since then
> and it probably would be pretty simple today to add a flag expire(obj,
> persistent_only=True) something like that.   But it's one of those
> things where when someone needs that, I want to see why, because I
> think there's usually another way to handle the situation.

[sqlalchemy] Suppress SQL statement in DBAPI Error Message?

[Rick Suggs]

Is there any way to suppress the SQL statement that is included in the 
DBAPI error message?

For example:

sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for 
integer: "hello"
LINE 1: INSERT INTO test (num, data) VALUES (100, 'hello');
  ^
 [SQL: "INSERT INTO test (num, data) VALUES (100, 'hello');"]

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] almbic postgres text_pattern_ops on index

[kris]

Try to create a postgres specific option while creating index , pointer 
appreciated

this link 
http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.create_index
 
suggest I can pass dialect specific options

def upgrade():
op.drop_index('ix_taggable_resource_value', 'taggable')
op.create_index('ix_taggable_resource_value', 'taggable', [ 
'resource_value' ],
postgresql_ops = { 'resource_value', 'text_pattern_ops' 
} )


NFO  [alembic.migration] Running upgrade 2cbd9f4d11af -> 318381ec60ba, 
indexes 4 deletes
Traceback (most recent call last):
  File "/home/kgk/Envs/bq059/bin/alembic", line 11, in 
sys.exit(main())
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/config.py", 
line 298, in main
CommandLine(prog=prog).main(argv=argv)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/config.py", 
line 293, in main
self.run_cmd(cfg, options)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/config.py", 
line 279, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/command.py", 
line 124, in upgrade
script.run_env()
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/script.py", 
line 199, in run_env
util.load_python_file(self.dir, 'env.py')
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/util.py", 
line 205, in load_python_file
module = load_module_py(module_id, path)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/compat.py", 
line 58, in load_module_py
mod = imp.load_source(module_id, path, fp)
  File "./migrations/env.py", line 76, in 
run_migrations_online()
  File "./migrations/env.py", line 69, in run_migrations_online
context.run_migrations()
  File "", line 7, in run_migrations
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/environment.py",
 
line 681, in run_migrations
self.get_context().run_migrations(**kw)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/migration.py", 
line 225, in run_migrations
change(**kw)
  File "./migrations/versions/318381ec60ba_indexes_4_deletes.py", line 22, 
in upgrade
postgresql_ops = { 'resource_value', 'text_pattern_ops' } )
  File "", line 7, in create_index
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/operations.py", 
line 698, in create_index
self._index(name, table_name, columns, schema=schema, **kw)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/ddl/impl.py", 
line 161, in create_index
self._exec(schema.CreateIndex(index))
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/alembic/ddl/impl.py", 
line 76, in _exec
conn.execute(construct, *multiparams, **params)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 
line 945, in execute
return meth(self, multiparams, params)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", 
line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 
line 996, in _execute_ddl
if not self.schema_for_object.is_default else None)
  File "", line 1, in 
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
 
line 436, in compile
return self._compiler(dialect, bind=bind, **kw)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", 
line 26, in _compiler
return dialect.ddl_compiler(dialect, self, **kw)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 
line 216, in __init__
self.string = self.process(self.statement, **compile_kwargs)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 
line 242, in process
return obj._compiler_dispatch(self, **kwargs)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
 
line 81, in _compiler_dispatch
return meth(self, **kw)
  File 
"/home/kgk/Envs/bq059/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py",
 
line 1670, in visit_create_index
for expr in index.expressions
TypeError: 'set' object has no attribute '__getitem__'
An Unknown exception occured There was a problem initializing the Database

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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, 

Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql

[Mike Bayer]

On Thu, Jan 18, 2018 at 12:31 PM, Lukasz Szybalski 
wrote:

> Hello,
> I have a query in sqlalchemy like below where I lookup contract# in mssql.
> How do I enforce the varchar instead of nvarchar? I tried converting my
> field to "str(mycurrent)" but that didn't do anything. Is there some other
> spot to force VARCHAR to be sent?
>


this just came up in
https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
in that case they are using typed parameters:

String(50, convert_unicode='force')

if you're dealing w/ the raw string like that try this:

execute(text("select ... where foo =
:mycurrent").bindparams(bindparam("mycurrent",
type_=String(convert_unicode='force')))

let me know if that works b.c. this has to be in the docs




>
> You can find that the query that uses NVARCHAR does an index scan has
> 30,909 logical reads on the dbo.P table.  It also uses 890 ms of CPU and
> has a total elapsed time of 938 ms.
>
> The query that uses VARCHAR does an index seek and has 7 logical reads on
> the dbo.P table.  It uses 0 ms of CPU and has a total elapsed time of 11 ms.
>
>
>  p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id  inner
> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
> where p.current=:mycurrent and D.srtype_id =5",params={'mycurrent':str(
> mycurrent)}).fetchall()
>
>
>
>
>
> [image: Inline image 1]
>
>
>
>
> Thank you
>
> Lucas
>
>
>
>
> --
> http://lucasmanual.com/ 
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] NVARCHAR vs VARCHAR in mssql

[Lukasz Szybalski]

Hello,
I have a query in sqlalchemy like below where I lookup contract# in mssql.
How do I enforce the varchar instead of nvarchar? I tried converting my
field to "str(mycurrent)" but that didn't do anything. Is there some other
spot to force VARCHAR to be sent?


You can find that the query that uses NVARCHAR does an index scan has
30,909 logical reads on the dbo.P table.  It also uses 890 ms of CPU and
has a total elapsed time of 938 ms.

The query that uses VARCHAR does an index seek and has 7 logical reads on
the dbo.P table.  It uses 0 ms of CPU and has a total elapsed time of 11 ms.


 p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock)
inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id  inner join dbo.D D
with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where
p.current=:mycurrent and D.srtype_id
=5",params={'mycurrent':str(mycurrent)}).fetchall()





[image: Inline image 1]




Thank you

Lucas




-- 
http://lucasmanual.com/ 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Re: Temporarily disable DB persistence for optimization routine

[Ruben Di Battista]

I confirm what I said. 

The run in multiprocessing was regenerating instances because after 
deserialization they were getting new IDs. I tried to implement a custom 
__hash__ but it seems that SQLAlchemy does not get it. 

What I did was disabling the backref cascade for `Satellite` and 
`GroundStation` objects and then, after optimization, doing:

for passage in results:
# I need to merge since if coming from multiprocessing the instance
# IDs change.
passage.satellite = session.merge(passage.satellite)
passage.ground_station = session.merge(passage.ground_station)
session.add(passage)

This looks working as expected. 

Thanks to Mike and Simon pointing me on the right track!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Re: exception message encoded in utf8

[Nico C.]

I think I can help reproduce this, but one has to configure the base system 
in a non C or english locale.
E.g. the system I work with is in french: it's default locale is 
fr_FR.UTF-8, hence the postgresql server
I installed on it runs with that locale too, by default. One can check with 
the `SHOW lc_messages;` SQL query.

Hence, with any error I trigger on the server side, like an IntegrityError 
or a ProgrammingError, comes
with a message from the server, which is encoded in that locale. For 
example many of messages translated
in french, come with «» (french *guillemets*) instead of double quotes 
which have psycopg or SQLAlchemy
choke on it. I would agree that is looks more like psycopg issue. It also 
rarely impacts production environments,
which are generally not set with fancy locale (at least I don't do that).

For reference, some ways to mitigate this are:

- reconfigure your postgresql serveur and have it use another default 
(requires "administrative" access to reconfigure the server);
- reconfigure your session so it use another session (cf. code snippet 
below, requires to be superuser which is rarely convenient);

Regarding the required priveleges cf. 
https://www.postgresql.org/docs/current/static/runtime-config-client.html.

@listens_for(_engine, 'connect')
def first_connect_callback(dbapi_connection, connection_record):
# Registers composite types so psycopg2 know how to
# handle them when they appear in resultsets.
cursor = dbapi_connection.cursor()

cursor.execute("SET lc_messages TO 'C';")
cursor.execute("COMMIT;")
cursor.execute("BEGIN;")  # Make sure you start a new transaction (may 
not always be appropriate)


Le jeudi 20 décembre 2012 18:35:13 UTC+1, Michael Bayer a écrit :
>
> OK as I said earlier, I'm not able to reproduce this.So I'd need that 
> reproduction case in order to do anything.   To be honest it sounds more 
> like a psycopg2 bug, since psycopg2 does the decoding in most cases 
> nowadays and even works with Python 3, so for it to be raising an exception 
> with the "bytes" type for the message is certainly a bug.   But would need 
> to see a real world example to get a feel for it.
>
>
> On Dec 20, 2012, at 7:03 AM, Sylvain Prat wrote:
>
> Sorry to ressurrect this thread but the problem is still there. Since 
> SQLAlchemy knows the encoding used to communicate with the database, it can 
> properly decode the error strings returned by the database to unicode. So, 
> I think it should be SQLAlchemy's responsibility to convert the error 
> strings to unicode, not the user's responsibility. Could we open a bug for 
> that in the tracker?
>
> Sylvain
>
>
> Le vendredi 7 mars 2008 02:39:56 UTC+1, jean-philippe dutreve a écrit :
>>
>> On 7 mar, 02:29, Michael Bayer  wrote: 
>>
>> > logging module itself throws UnicodeDecodeError ? 
>> yes, in logging.format: ... = "%s" % msg 
>> with msg the exception message encoded in utf8 and the default 
>> encoding is ascii. 
>>
>> > are you sending exception messages using logging.debug() or similar ? 
>> exactly: log.error("... : %s", e.message) 
>>
>> my impression 
>> > is that you'd want to decode those manually doing something like 
>> > string.decode('utf-8'). 
>> yes, it works fine, but it's pain to do this in each try/except. 
>> Another solution is setting utf8 as the default encoding in 
>> sitecustomize.py. 
>> It's better centralized, but has sitepackage effect. 
>>
>> But a better way IMHO is that the DB driver or SA returns unicode 
>> exception message. 
>>
>
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/OOEvbKoo63cJ.
> To post to this group, send email to sqlal...@googlegroups.com 
> .
> To unsubscribe from this group, send email to 
> sqlalchemy+...@googlegroups.com .
> For more options, visit this group at 
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.