Re: [sqlalchemy] Handle anticipated data truncation before transaction commits

[Jonathan Vanasco]

On Friday, November 3, 2017 at 12:03:43 AM UTC-4, jens.t...@gmail.com wrote:
>
> Thanks Mike!  Considering we're planning to migrate to PostgreSQL in a 
> month or two, how would I go about that (considering there's no strict mode 
> there). Would the exception be raised on PostgreSQL?
>

A manual check is robably best, but the exception is triggered on the 
flush() when executed in PostgreSQL.  

Mysql is a bit weird in that it does two things different than most other 
databases:

1. It allows for sql standards to be disabled
2. the default behavior is to have sql standards disabled.

There are a lot of weird behaviors because of this, and I wouldn't be 
surprised if generating this error is deferred in a transaction.  Various 
behaviors are also different based on the table engine you use too.

Since you said this is happening in Pyramid, I suggest running a quick 
standalone script to ensure the error is happening on the commit and not 
flush.  There is a very small chance you have something setup in Pyramid 
that is causing the error to bubble up like this.

-- 
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] Handle anticipated data truncation before transaction commits

[jens . troeger]

Hm. I enabled strict mode, and sure enough the warning turns into an 
exception. 

Alas, it seems that the flush() in the above example does not cause the 
exception, it happens later when the query executes. I was looking for a 
way to preempt that exception: the code runs within a view function of a 
Pyramid view handler which commits the transaction outside of the view 
function.

Looks like a manual check is needed :-)


On Friday, November 3, 2017 at 12:04:54 PM UTC+10, Mike Bayer wrote:
>
> the most appropriate way is to set strict SQL mode on your MySQL database: 
>
> https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict 
>   
>

-- 
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] Handle anticipated data truncation before transaction commits

[jens . troeger]

Thanks Mike!  Considering we're planning to migrate to PostgreSQL in a 
month or two, how would I go about that (considering there's no strict mode 
there). Would the exception be raised on PostgreSQL?

Jens


On Friday, November 3, 2017 at 12:04:54 PM UTC+10, Mike Bayer wrote:
>
> > What is the proper way of handling this situation, other than comparing 
> the 
> > string length against the column size explicitly? 
>
> the most appropriate way is to set strict SQL mode on your MySQL database: 
>
> https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict 
>
>

-- 
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] Handle anticipated data truncation before transaction commits

[Mike Bayer]

On Thu, Nov 2, 2017 at 9:27 PM,   wrote:
> Hi,
>
> I've got a db object with a string property that may grow larger than its
> column specifies (because it's being appended to). I tried:
>
> old_string = obj.string
> try:
> obj.string = "abc" * 1
> dbsession.flush()
> except Exception as e:
> obj.string = old_string
>
> but the expected warning wasn't caught here:
>
> /…/pymysql/cursors.py:166: Warning: (1265, "Data truncated for column 'chat'
> at row 1")
>   result = self._query(query)
>
> What is the proper way of handling this situation, other than comparing the
> string length against the column size explicitly?

the most appropriate way is to set strict SQL mode on your MySQL database:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict





>
> Thanks!
> Jens
>
> --
> 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] Handle anticipated data truncation before transaction commits

[jens . troeger]

Hi,

I've got a db object with a string property that may grow larger than its 
column specifies (because it's being appended to). I tried:

old_string = obj.string
try:

obj.string = "abc" * 1  
   
dbsession.flush()  
 
except Exception as e:  

obj.string = old_string
 

but the expected warning wasn't caught here:

/…/pymysql/cursors.py:166: Warning: (1265, "Data truncated for column 
'chat' at row 1")
  result = self._query(query)

What is the proper way of handling this situation, other than comparing the 
string length against the column size explicitly?

Thanks!
Jens

-- 
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] Declarative API without state management

[Mike Bayer]

On Thu, Nov 2, 2017 at 3:09 PM, Max Rothman  wrote:
> I was poking around Elixir's Ecto, and I was curious whether it was possible
> to apply their strict separation between data and operations to SQLAlchemy.
> One could imagine an API where session.query fetches mutable, data-only
> versions of models, and models are updated with something like
> session.update(model), which would be a proxy for
> update(Model).where(Model.id == model.id). In that scenario, the only
> stateful element of the session would be the status of the current
> transaction.
>
> Is this sort of thing possible? Can the declarative API be used without
> binding model classes to a session?

The declarative API and the Mapper objects it creates have no
dependency on a Session being present at all.   So you're free to
build a new kind of Session that doesn't use a unit of work, sure.


> Can session state management be turned
> off, or could the connection be used directly?

It depends where you want to make the cutoff.   You can build out this
new ORM using Core where you'd have Connection and all that.  If you
want to still use Query then you'd have to subclass that also and
provide an alternate loading scheme (called from
_execute_and_instances()).

I don't really see how this approach is *worth* it, after all if you
just session.update(model), what about all the things which refer to
it and for which it depends on that may not have been "updated".   You
have to hand-wire all that in your code, which would ultimately lead
to inventing some new system of automating it all, which means you
just reinvent the whole thing.

SQLAlchemy even started with a little bit of this "separation"
idealism, which is why it has this thing nobody uses anymore called
"classical mapping" - the idea that the class definitions would know
nothing about how they are persisted.  Sounds great until you realize
the class needs to refer to its own state and the attributes within
that state are determined by the table schema.   We used to have
querying work as "MyClass.c.col == 'value'", e.g. the ".c." was there
as a means of "separating" the "persistence" from the "object model".
 When we changed it to just be "MyClass.col", the library became
massively easier to use and allowed the whole brilliance of "hybrid
properties" that everyone now loves to come forth.If there were
ever a SQLAlchemy 2, "classical mapping" as an option and
"declarative" as only an "extension" would be at the very top of the
list of things to go.   Decisions made in the name of "separation of
concerns", sometimes critical but always very risky.


>
> Thanks,
> Max
>
> --
> 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] Declarative API without state management

[Max Rothman]

I was poking around Elixir's Ecto , and 
I was curious whether it was possible to apply their strict separation 
between data and operations to SQLAlchemy. One could imagine an API where 
session.query fetches mutable, data-only versions of models, and models are 
updated with something like session.update(model), which would be a proxy 
for update(Model).where(Model.id == model.id). In that scenario, the only 
stateful element of the session would be the status of the current 
transaction.

Is this sort of thing possible? Can the declarative API be used without 
binding model classes to a session? Can session state management be turned 
off, or could the connection be used directly?

Thanks,
Max

-- 
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] Possible inconsistency between schema names and FKs when inspecting SQLite

[Jacopo Farina]

I ran your code and worked perfectly, I was using "sqlite+pysqlite" instead 
of "sqlite" as the engine but the behavior remains the same.

Examining the example I discovered the inspector was correctly retrieving 
the foreign keys using 'main' as a schema but filtering them out later, 
sorry for making you waste time -_-

Thanks a lot!

Jacopo

On Thursday, 2 November 2017 02:45:56 UTC+1, Mike Bayer wrote:
>
> On Wed, Nov 1, 2017 at 6:43 PM, Jacopo Farina  > wrote: 
> > Hello everybody, 
> > 
> > I am writing a component of an application that should display a chart 
> of 
> > database tables and foreign keys (this one), and trying to make it 
> database 
> > agnostic. I encountered a strange behavior when inspecting the foreign 
> keys 
> > of a SQLite database, in particular I can't see them by setting 
> schema_name 
> > to the value returned from get_schema_names but only by passing 
> > schema_name=None. 
> > 
> > I create a test db this way: 
> > 
> > conn = sqlite3.connect('test.db') 
> > conn.execute('CREATE TABLE table_one(column_one INTEGER, column_two 
> TEXT)') 
> > conn.execute("""CREATE TABLE table_two( 
> > column_X INTEGER, 
> > column_Y TEXT, 
> > FOREIGN KEY(column_X) REFERENCES table_one(column_one) 
> > )""") 
> > conn.close() 
> > 
> > 
> > If I use a sqlite database I see that 
> > 
> > inspect(engine).get_schema_names() 
> > 
> > returns ['main'] and 
> > 
> > sa.engine.reflection.Inspector.from_engine(db).get_table_names('main') 
> > 
> > shows the tables in the database, as expected. 
> > 
> > But when I run 
> > 
> sa.engine.reflection.Inspector.from_engine(db).get_foreign_keys('table_name', 
>
> > schema='main') 
> > 
> > it doesn't return any foreign key, while it does with schema=None 
> > 
> > is this a bug or the expected behavior? 
>
> can't reproduce your result with any of SQLAlchemy version 1.0, 1.1, 
> or current master.  Here's a test case against 1.1's format, if you 
> are using 1.0 remove the "options" element from the assertion list: 
> import sqlite3 
>
> import os 
>
> if os.path.exists('test.db'): 
> os.unlink('test.db') 
>
> conn = sqlite3.connect('test.db') 
> conn.execute('CREATE TABLE table_one(column_one INTEGER, column_two 
> TEXT)') 
> conn.execute("""CREATE TABLE table_two( 
> column_X INTEGER, 
> column_Y TEXT, 
> FOREIGN KEY(column_X) REFERENCES table_one(column_one) 
> )""") 
> conn.close() 
>
>
> from sqlalchemy import create_engine, inspect 
> import sqlalchemy as sa 
>
> engine = create_engine("sqlite:///test.db", echo='debug') 
>
> for table_name in ("table_one", "table_two"): 
> print("1. -") 
> fk_no_schema = sa.engine.reflection.Inspector.\ 
> from_engine(engine).get_foreign_keys(table_name) 
> print(fk_no_schema) 
>
> print("2. -") 
> fk_schema = sa.engine.reflection.Inspector.\ 
> from_engine(engine).get_foreign_keys(table_name, schema='main') 
> print(fk_schema) 
>
> if table_name == "table_two": 
> assert fk_no_schema == [ 
> { 
> 'name': None, 
> 'referred_columns': [u'column_one'], 
> 'referred_table': u'table_one', 
> 'constrained_columns': [u'column_X'], 
> 'referred_schema': None, 
> 'options': {}} 
> ] 
> assert fk_schema == [ 
> { 
> 'name': None, 
> 'referred_columns': [u'column_one'], 
> 'referred_table': u'table_one', 
> 'constrained_columns': [u'column_X'], 
> 'referred_schema': 'main', 
> 'options': {}} 
> ] 
>
>
> the output of each "table2" without the SQL echoing is: 
>
> [{'name': None, 'referred_columns': [u'column_one'], 'referred_table': 
> u'table_one', 'constrained_columns': [u'column_X'], 'referred_schema': 
> None, 'options': {}}] 
> [{'name': None, 'referred_columns': [u'column_one'], 'referred_table': 
> u'table_one', 'constrained_columns': [u'column_X'], 'referred_schema': 
> 'main', 'options': {}}] 
>
> the only difference is that when you ask for the FKs in terms of the 
> "main" schema, vs. the "default" schema (which is the same thing, just 
> implicit), it maintains that as the "referred_schema" argument in the 
> returned list. 
>
> Things which may impact the behavior of SQLite are: 
>
> SQLAlchemy version 
> Python / pysqlite version 
> sqlite3.so version underlying the pysqlite version 
> operating system 
>
> otherwise if you can confirm the above test case works, the job is to 
> figure out what's different about your original run vs. this one. 
>
>
> > 
> > 
> > -- 
> > 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