On Wed, May 25, 2011 at 12:48 AM, Chris Withers <[email protected]> wrote:
> On 25/05/2011 07:41, saturngod wrote:
>>
>> Now, I'm thinking about to change pyramid from PHP. I'm using advance
>> sql query for searching
>>
>> Current mysql is using
>>
>> SELECT * , IF( `Word` = 'sim', 1, IF( `Word` LIKE 'sim%', 2,
>> IF( `Word` LIKE '%sim', 4, 3 ) ) ) AS `sort` FROM `dblist` WHERE
>> `Word` LIKE '%sim%' ORDER BY `sort` , `Word`;
>
> My guess is you can probably come up with a better way of doing this ;-)
SQLAlchemy has several levels. At the lowest level, you can execute
the entire query above as a string:
``engine.execute(sql)``, ``connection.execute(sql)``, or
``Session.execute(sql)``. At the middle level (called the SQL level),
you build up the SQL expression pythonically. You'll have a Table
object ``dblist`` which knows all the columns. (You can define the
columns explicitly or reflect them from an existing table.)
import sqlalchemy as sa
engine = sa.create_engine("mysql://user:password@localhost/database")
conn = engine.connect()
md = sa.MetaData()
dblist = sa.Table("dblist", md, autload=True, autoload_with=conn)
dc = dblist.columns
where = dc.Word.like("%sim%")
sort = sa.func.if(
dc.Word.like("sim%"), 2, sa.func.if(
dc.Word.like("%sim"), 4, 3)).label("sort")
sql = sa.select([dblist, dc.AnotherColumn, word], where).order_by(sort, dc.Word)
# ``str(sql)`` would produce the SQL string
rslt = conn.execute(sql)
rows = rslt.fetchall()
-- or --
for row in rslt:
...
I've added a column "AnotherColumn" as an example of selecting a
specific column.
That 'if' expression is convoluted and may be MySQL-specific.
Alternatives include the SQL 'case' construct or something else. A
direct conversion of that expression would be:
sort = sa.case([
(dc.Word.like("sim%"), 2),
(dc.Word.like(%sim"), 4),
], else_=3).label("sort")
``sa.func.FOO(...)`` generically calls any SQL function. SQLAlchemy
has special functions for certain esoteric functions, and it also has
constructs for "column starts with" and "column contains". It
converts these to equivalent 'like' expressions. See
http://www.sqlalchemy.org/docs/core/expression_api.html
With this knowledge, we can make a simpler construct with additional columns:
is_start = dc.Word.startswith("sim").label("is_start")
is_end = dc.Word.endswith("sim").label("is_end")
fields = [dblist, db.AnotherColumn, is_start, is_end]
where = dc.Word.contains("sim")
sql = sa.select(fields, where).order_by(is_start.desc(), is_end, dc.Word)
True sorts after false so I had to sort the first column descending.
We can make a little program to verify our assumptions. Attached is
the program, and its output for MySQL, Postgresql, and SQLite.
The results show different ordering of 'sim' vs 'simulate' in the
different databases. This may be a genuine difference in how the
engines handle string values, or it may be a flaw in my logic.
The third level in SQLAlchemy, the highest level, is the ORM. Most
applications use this level. At this level you make a Python class for
each table, and build queries using the classes. The results are
instances of these classes (i.e., rows in the database). You can also
query for specific columns or calculated fields, in which case the
results are RowProxies just like at the SQL level.
# Initialize the ORM
import sqlalchemy.orm as orm
class DBList(object):
pass
orm.mapper(DBList, dblist)
Session = orm.sessionmaker(bind=engine)
sess = Session()
# Query all DBList records
q = sess.query(DBList)
for item in q: # Runs query
print q.word
# Query all DBList records and a boolean expression (This returns tuples.)
q = sess.query(DBList, DBList.word.startswith("sim"))
for r in q:
print r[0] # The first DBList instance, I think
print r[1] # The result of the startswith expression
# Add a new record
new = DBList()
new.word = "foo")
sess.insert(new)
sess.commit()
You can also initialize the ORM class and table simultaneously using
the "declarative" syntax:
import sqlalchemy.ext.declarative as declarative
Base = declarative.declarative_base()
class DBList(Base):
__tablename__ = "dblist"
word = sa.Column(sa.types.String(255), nullable=False, primary_key=True)
Now the ORM class is ``DBList``, the table is ``DBList.__table__``,
and the metadata is ``Base.metadata``. You can use these exactly like
the equivalent objects above.
The data structures may look complex at first, but building your SQL
in an object-oriented manner makes it easy to substitute values,
reconfigure expressions, and introspect the parts of the objects
whenever you need to.
--
Mike Orr <[email protected]>
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:SELECT DATABASE()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:SHOW VARIABLES LIKE
'character_set%%'
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:SHOW VARIABLES LIKE
'lower_case_table_names'
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:SHOW COLLATION
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:SHOW VARIABLES LIKE 'sql_mode'
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:DESCRIBE `dblist`
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:
DROP TABLE dblist
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:
CREATE TABLE dblist (
word VARCHAR(80) NOT NULL
)
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:()
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:INSERT INTO dblist (word) VALUES
(%s)
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:(('timsimdim',), ('foobar',),
('electrosim',), ('sim',), ('simulate',))
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:SELECT dblist.word, dblist.word
LIKE concat(%s, '%%') AS is_start, dblist.word LIKE concat('%%', %s) AS is_end
FROM dblist
WHERE dblist.word LIKE concat(concat('%%', %s), '%%') ORDER BY (dblist.word
LIKE concat(%s, '%%')) DESC, dblist.word LIKE concat('%%', %s), dblist.word
INFO:sqlalchemy.engine.base.Engine.0x...cb8c:('sim', 'sim', 'sim', 'sim', 'sim')
Final results for columns [word, is_start, is_end]:
[('simulate', True, False),
('sim', True, True),
('timsimdim', False, False),
('electrosim', False, True)]
import logging
import pprint
import sys
import sqlalchemy as sa
md = sa.MetaData()
dblist = sa.Table("dblist", md,
sa.Column("word", sa.String(80), nullable=False),
)
def init_tables(conn):
dblist.drop(bind=conn, checkfirst=True)
dblist.create(bind=conn)
insert = dblist.insert()
records = [
{"word": "timsimdim"},
{"word": "foobar"},
{"word": "electrosim"},
{"word": "sim"},
{"word": "simulate"},
]
conn.execute(insert, records)
def main():
logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
if len(sys.argv) != 2:
sys.exit("usage: %s DBURL" % sys.argv[0])
dburl = sys.argv[1]
engine = sa.create_engine(dburl)
conn = engine.connect()
init_tables(conn)
dc = dblist.columns
is_start = dc.word.startswith("sim").label("is_start")
is_end = dc.word.endswith("sim").label("is_end")
where = dc.word.contains("sim")
fields = [dblist, is_start, is_end]
sql = sa.select(fields, where).order_by(
is_start.desc(), is_end, dc.word)
rows = conn.execute(sql).fetchall()
print
print "Final results for columns [word, is_start, is_end]:"
pprint.pprint(rows)
if __name__ == "__main__": main()
INFO:sqlalchemy.engine.base.Engine.0x...964c:select version()
INFO:sqlalchemy.engine.base.Engine.0x...964c:{}
INFO:sqlalchemy.engine.base.Engine.0x...964c:select current_schema()
INFO:sqlalchemy.engine.base.Engine.0x...964c:{}
INFO:sqlalchemy.engine.base.Engine.0x...964c:select relname from pg_class c
join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema()
and lower(relname)=%(name)s
INFO:sqlalchemy.engine.base.Engine.0x...964c:{'name': u'dblist'}
INFO:sqlalchemy.engine.base.Engine.0x...964c:
CREATE TABLE dblist (
word VARCHAR(80) NOT NULL
)
INFO:sqlalchemy.engine.base.Engine.0x...964c:{}
INFO:sqlalchemy.engine.base.Engine.0x...964c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...964c:INSERT INTO dblist (word) VALUES
(%(word)s)
INFO:sqlalchemy.engine.base.Engine.0x...964c:({'word': 'timsimdim'}, {'word':
'foobar'}, {'word': 'electrosim'}, {'word': 'sim'}, {'word': 'simulate'})
INFO:sqlalchemy.engine.base.Engine.0x...964c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...964c:SELECT dblist.word, dblist.word
LIKE %(word_1)s || '%%' AS is_start, dblist.word LIKE '%%' || %(word_2)s AS
is_end
FROM dblist
WHERE dblist.word LIKE '%%' || %(word_3)s || '%%' ORDER BY (dblist.word LIKE
%(word_1)s || '%%') DESC, dblist.word LIKE '%%' || %(word_2)s, dblist.word
INFO:sqlalchemy.engine.base.Engine.0x...964c:{'word_3': 'sim', 'word_2': 'sim',
'word_1': 'sim'}
Final results for columns [word, is_start, is_end]:
[(u'simulate', True, False),
(u'sim', True, True),
(u'timsimdim', False, False),
(u'electrosim', False, True)]
INFO:sqlalchemy.engine.base.Engine.0x...2bac:PRAGMA table_info("dblist")
INFO:sqlalchemy.engine.base.Engine.0x...2bac:()
INFO:sqlalchemy.engine.base.Engine.0x...2bac:
CREATE TABLE dblist (
word VARCHAR(80) NOT NULL
)
INFO:sqlalchemy.engine.base.Engine.0x...2bac:()
INFO:sqlalchemy.engine.base.Engine.0x...2bac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...2bac:INSERT INTO dblist (word) VALUES
(?)
INFO:sqlalchemy.engine.base.Engine.0x...2bac:(('timsimdim',), ('foobar',),
('electrosim',), ('sim',), ('simulate',))
INFO:sqlalchemy.engine.base.Engine.0x...2bac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...2bac:SELECT dblist.word, dblist.word
LIKE ? || '%%' AS is_start, dblist.word LIKE '%%' || ? AS is_end
FROM dblist
WHERE dblist.word LIKE '%%' || ? || '%%' ORDER BY (dblist.word LIKE ? || '%%')
DESC, dblist.word LIKE '%%' || ?, dblist.word
INFO:sqlalchemy.engine.base.Engine.0x...2bac:('sim', 'sim', 'sim', 'sim', 'sim')
Final results for columns [word, is_start, is_end]:
[(u'simulate', True, False),
(u'sim', True, True),
(u'timsimdim', False, False),
(u'electrosim', False, True)]
