2015 m. kovas 23 d., pirmadienis 22:10:16 UTC+2, Michael Bayer rašė:
>
>
>
> Edgaras Lukoševičius <[email protected] <javascript:>> wrote:
>
> > A quick script with SQL table schema.
>
> won’t work with MySQL. The ability to send a pure string SQL statement
> directly to execute() in conjunction with a straight tuple, and have it
> magically expand out to an IN, is a psycopg2 / postgresql specific
> feature.
>
> > If tuple is a hack, then how should I it work with the same logic, but
> without tuple? Construct a string from tuple? I'm afraid that will be
> double quoted (sanitized) too.
>
> The SQLAlchemy Core is designed to produce SQL strings on the fly. You
> need
> only call “column.in_(collection)” and the correct IN clause with bound
> parameters will be generated dynamically.
>
>
Yes, I have already tried that, and while not ideal solution for my case
(some flexibility/simplicity will be lost), that is better than nothing :)
Thanks.
> see below:
>
> from sqlalchemy import create_engine, select, Table, MetaData
> from sqlalchemy.orm import sessionmaker
>
> #engine = create_engine('mysql://root@localhost/preferences', echo=False)
> engine = create_engine('mysql://scott:tiger@localhost/test', echo=True)
>
> engine.execute("""
> CREATE TABLE if not exists `preferences` (
> `recipient` varchar(255) COLLATE latin1_general_ci NOT NULL,
> `col1` tinyint(1) NOT NULL DEFAULT '1',
> `col2` tinyint(1) NOT NULL DEFAULT '1',
> `col3` tinyint(1) NOT NULL DEFAULT '1',
> PRIMARY KEY (`recipient`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
> """)
>
> m = MetaData()
> t = Table("preferences", m, autoload=True, autoload_with=engine)
>
> Session = sessionmaker(bind=engine)
> session = Session()
>
>
> recipients1 = ["recipient1"]
> recipients2 = ["recipient1", "recipient2", "recipient3"]
>
> sql_query = select([t.c.col1, t.c.col2, t.c.col3])
>
> user_configs = \
>
> session.execute(sql_query.where(t.c.recipient.in_(recipients1))).fetchall()
>
> print(user_configs)
>
> user_configs = \
>
> session.execute(sql_query.where(t.c.recipient.in_(recipients2))).fetchall()
>
>
> print(user_configs)
>
>
> output:
>
> BEGIN (implicit)
> 2015-03-23 16:08:57,188 INFO sqlalchemy.engine.base.Engine SELECT
> preferences.col1, preferences.col2, preferences.col3
> FROM preferences
> WHERE preferences.recipient IN (%s)
> 2015-03-23 16:08:57,188 INFO sqlalchemy.engine.base.Engine ('recipient1',)
> []
> 2015-03-23 16:08:57,189 INFO sqlalchemy.engine.base.Engine SELECT
> preferences.col1, preferences.col2, preferences.col3
> FROM preferences
> WHERE preferences.recipient IN (%s, %s, %s)
> 2015-03-23 16:08:57,189 INFO sqlalchemy.engine.base.Engine ('recipient1',
> 'recipient2', 'recipient3')
> []
>
>
>
>
>
>
> > from sqlalchemy import create_engine
> > from sqlalchemy.orm import sessionmaker
> >
> > engine = create_engine('mysql://root@localhost/preferences', echo=False)
> >
> > Session = sessionmaker(bind=engine)
> > session = Session()
> >
> > #CREATE TABLE `preferences` (
> > # `recipient` varchar(255) COLLATE latin1_general_ci NOT NULL,
> > # `col1` tinyint(1) NOT NULL DEFAULT '1',
> > # `col2` tinyint(1) NOT NULL DEFAULT '1',
> > # `col3` tinyint(1) NOT NULL DEFAULT '1',
> > # PRIMARY KEY (`recipient`)
> > #) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
> >
> >
> > recipients1=["recipient1"]
> > recipients2=["recipient1","recipient2","recipient3"]
> >
> > sql_query="SELECT col1, col2, col3 FROM preferences WHERE recipient IN
> :recipients"
> >
> > # multi value array
> > # this fail because of double quotes
> > user_configs = session.execute(sql_query,
> dict(recipients=tuple(recipients2))).fetchall()
> > print user_configs
> >
> > # single value array
> > # this fails because of mysql syntax errox (tuple inserted incorrectly)
> > user_configs = session.execute(sql_query,
> dict(recipients=tuple(recipients1))).fetchall()
> > print user_configs
> >
> >
> >
> >
> >
> > 2015 m. kovas 23 d., pirmadienis 16:45:48 UTC+2, Edgaras Lukoševičius
> rašė:
> > Hello,
> >
> > as I'm not receiving any responses in stackoverflow I wil try here. Can
> someone help me with this issue?
> >
> >
> http://stackoverflow.com/questions/29195825/sqlalchemy-double-quoting-list-items
>
> >
> > --
> > 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at http://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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
