未完成的沙盒脚本如下:
import os
import re
import sqlite3
#
# python sqlite
#
DB_PATH = __name__ + '.db'
try:
os.remove(DB_PATH)
except:
pass
def re_fn(expr, item):
reg = re.compile(expr, re.I)
return reg.search(item) is not None
conn = sqlite3.connect(':memory:')
conn = sqlite3.connect(DB_PATH)
conn.create_function("REGEXP", 2, re_fn)
cursor = conn.cursor()
cursor.execute(
'CREATE TABLE t1 (id INTEGER PRIMARY KEY, c1 TEXT)'
)
cursor.executemany(
#'INSERT INTO t1 (c1) VALUES (?)', [('aaa"test"',),('blah',)]
'INSERT INTO t1 (c1) VALUES (?)', [
('dupa / 1st Part',), ('cycki / 2nd Part',), ('fiut / 3rd Part',)
]
)
cursor.execute(
#'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['2|3\w+part']
'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['\d\w+ part']
)
conn.commit()
data=cursor.fetchall()
print(data)
#
# sqlalchemy
#
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
DSN = 'sqlite:///' + DB_PATH
engine = sa.create_engine(DSN, convert_unicode=True)
db = orm.scoped_session(orm.sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
Base = declarative_base(bind=engine)
meta = Base.metadata
class T1(Base):
__table__ = sa.Table('t1', meta, autoload=True)
print(db.query(T1).all())
我发现正则表达式函数应该在每个线程上注册:
http://permalink.gmane.org/gmane.comp.web.pylons.general/12742
但我无法采用链接中的解决方案到我的脚本中,而且它已经过时。
更新
我想查询这个:
cursor.execute(
#'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['2|3\w+part']
'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['\d\w+ part']
)
但在SQLAlchemy中。