MySQL's sql_mode (ORM)

[Staszek]

Hi

How do you set sql_mode when using SQLAlchemy ORM with MySQL?

For example, I would like to be able to do something equivalent to this:

SET sql_mode = 'STRICT_ALL_TABLES';

so as to get an error (instead of a warning) when string length exceeds
column size on INSERT.

Ideally I would like to be able to combine several SQL modes together.
List of available MySQL modes:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
.

Thanks!

--
http://people.eisenbits.com/~stf/
http://www.eisenbits.com/

OpenPGP: 80FC 1824 2EA4 9223 A986 DB4E 934E FEA0 F492 A63B

[Jonathan Rogers]

Staszek wrote:
> Hi
>
> How do you set sql_mode when using SQLAlchemy ORM with MySQL?
>
> For example, I would like to be able to do something equivalent to this:
>
> SET sql_mode = 'STRICT_ALL_TABLES';

I don't know if SQLAlchemy has any specific support for this MySQL
feature since I use PostgreSQL, but you can always execute arbitrary SQL
using text().


--
Jonathan Rogers

[Michael Bayer]

here’s a recipe for emitting that SQL on every connection, as well as right up front on first connect which is optional, though if you plan on changing ANSI_QUOTES would need to happen before the dialect checks on sql_mode:

from sqlalchemy import create_engine, event

eng = create_engine("mysql://scott:tiger@localhost/test", echo='debug')

@event.listens_for(eng, "first_connect", insert=True) # make sure we're the very first thing
@event.listens_for(eng, "connect")
def connect(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'")

conn = eng.connect()

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

[Jonathan Vanasco]

you can pass custom connect arguments to the DBAPI in `create_engine`.

    http://docs.sqlalchemy.org/en/rel_0_9/faq.html?#how-do-i-pass-custom-connect-arguments-to-my-database-api

depending on which driver you use, it will be different.

you shouldn't use `text`, because sqlalchemy autoconfigures on connect to the sql_mode ( which can have different quoting styles )

[Jonathan Vanasco]

just to clarify...

mysqldb - `connect` accepts a `sql_mode` string:

    http://mysql-python.sourceforge.net/MySQLdb.html

pymysql accepts it too

    https://github.com/PyMySQL/PyMySQL/blob/3576863f9cd0b66ce6c8b32ab3448ab68f55f489/pymysql/connections.py

oursql: 

   i couldn't find it  

mysql-connector 

    i couldn't find it associated with 'connect', only during runtime

[jens.t...@gmail.com]

If I were to go into my MySQL DB and

mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

would that have the same effect? I find the MySQL documentation somewhat lacking on that topic. What are the scope and lifetime of the above vs. using a listener as suggested by Michael?

[Simon King]

On Tue, Feb 6, 2018 at 7:08 PM, <jens.t...@gmail.com> wrote:
> If I were to go into my MySQL DB and
>
> mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES';
>
> would that have the same effect? I find the MySQL documentation somewhat
> lacking on that topic. What are the scope and lifetime of the above vs.
> using a listener as suggested by Michael?
>

I'm pretty sure variables set using "SET GLOBAL" will not survive a
database restart. You'd need to specify it on the server command line
or config file if you wanted it to be persistent.

Simon