Edgaras Lukoševičius <[email protected]> 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.
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].
> 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.
--
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.
