2015 m. kovas 23 d., pirmadienis 22:10:16 UTC+2, Michael Bayer rašė: > > > > Edgaras Lukoševičius <edgaras.lu...@gmail.com <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 sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.