def __init__(self,config,session,engine):
self.config=config
#self.engine = create_engine(r'sqlite:///{}\{}.db'.format(self.config['DATABASE_URL'],self.config['dbname']))
#self.engine = create_engine(r'sqlite:///{}.db'.format(self.config['dbname']))
#self.engine=create_engine('sqlite:///:memory:user.db')
#Session = sessionmaker(bind=self.engine)
#self.session = Session()
#Base = declarative_base()
self.engine=engine
self.session=session
self.index_service = IndexService(config=self.config, session=self.session)
if config['dbname']=='user':
self.db=UserDB
elif config['dbname']=='agent':
self.db=AgentDB
self.register_db()
class dbUtils(object):
def __init__(self,config,session,engine):
self.config=config
#self.engine = create_engine(r'sqlite:///{}\{}.db'.format(self.config['DATABASE_URL'],self.config['dbname']))
#self.engine = create_engine(r'sqlite:///{}.db'.format(self.config['dbname']))
#self.engine=create_engine('sqlite:///:memory:user.db')
#Session = sessionmaker(bind=self.engine)
#self.session = Session()
#Base = declarative_base()
self.engine=engine
self.session=session
self.index_service = IndexService(config=self.config, session=self.session)
if config['dbname']=='user':
self.db=UserDB
elif config['dbname']=='agent':
self.db=AgentDB
self.register_db()
def register_db(self):
Base.metadata.create_all(self.engine)
self.index_service.register_class(self.db)
def add_documents(self,dict_):
try:
print('add')
ins=self.db(**dict_)
self.session.add(ins)
self.session.commit()
#self.session.rollback()
#self.session.close()
except Exception as e:
print('add exce',e)
self.session.rollback()
self.session.close()
# self.close()
def search_index(self,query):
return list(self.db.search_query(query))
def dataframe(self,dbname):
path=self.self.config['DATABASE_URL']+self.config['dbname']+'.db'
conn=sqlite3.connect(path)
df=pd.read_sql('select * from {}'.format(dbname),conn)
conn.close()
return df
def export_csv(self,csvfilename):
return self.dataframe(self.config['dname']).to_csv(csvfilename,index=False)
def __init__(self,
*,
app,
database_uri='sqlite:///test.db',
index_path='.',
**args):
self.app = app
self.database_uri = database_uri
self.index_path = index_path
configure_mappers()
self.engine = create_engine(self.database_uri)
self.Session = sessionmaker(self.engine,
autoflush=True,
expire_on_commit=True)
self.session = self.Session()
Base.metadata.create_all(self.engine)
self.index = IndexService(whoosh_base=self.index_path,
session=self.session)
self.index.register_class(Article)
def setup(context, session=None):
"""Setup the whooshalchemy index service"""
conf = json.load(open('config.json', 'rb'))[context]
index_service = IndexService(conf, session=session)
index_service.register_class(Meme)
index_service.register_class(Action)
return index_service
def srch_documents(dbname, query):
"""
dbname='userdb' or agentdb
"""
user_schema = ['id', 'name', 'mobile_number', 'email', 'date', 'comments']
agent_schema = [
'id', 'category', 'name', 'location', 'whatsapp', 'mobile_number',
'email', 'always'
]
print(dbname)
engine = create_engine('sqlite:///{}.db'.format(dbname))
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
index_service = IndexService(config=config, session=session)
res_list = []
if dbname == 'userdb':
index_service.register_class(UserDB)
db = UserDB
for cl in list(db.search_query(query)):
res = dict()
for col in user_schema:
res[col] = cl.__dict__[col]
res_list.append(res)
# res['id']=UserDB.__dict__['id']
# res['name']=UserDB.__dict__['name']
# res['mobile_number']=UserDB.__dict__['mobile_number']
# res['email']=UserDB.__dict__['email']
# res['date']=UserDB.__dict__['date']
# res['comments']=UserDB.__dict__['comments']
#add=UserDB(**update_dict)
elif dbname == 'agentdb':
index_service.register_class(AgentDB)
db = AgentDB
for cl in list(db.search_query(query)):
res = dict()
for col in agent_schema:
res[col] = cl.__dict__[col]
res_list.append(res)
return res_list
def add_documents(dbname, update_dict):
"""
dbname='userdb' or agentdb
"""
print(dbname)
engine = create_engine('sqlite:///{}.db'.format(dbname))
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
index_service = IndexService(config=config, session=session)
if dbname == 'userdb':
index_service.register_class(UserDB)
add = UserDB(**update_dict)
elif dbname == 'agentdb':
print(dbname, 'agent')
index_service.register_class(AgentDB)
add = AgentDB(**update_dict)
#m = BlogPost(title=u'My machines are dunp', content=u'learn databases quickly This is the first post.')
session.add(add)
session.commit()
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import IntegrityError
from config import SQLALCHEMY_DATABASE_URI
from whooshalchemy import IndexService
Base = declarative_base()
engine = create_engine(SQLALCHEMY_DATABASE_URI)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
config = {"WHOOSH_BASE": "whoosh"}
index_service = IndexService(config=config, session=session)
class Admin(Base):
__tablename__ = 'admin'
id = Column(Integer, primary_key=True)
username = Column(String(30), unique=True)
is_super = Column(Boolean, default=False)
def get_by_username(username):
admin = session.query(Admin).filter_by(username=username).first()
return admin
@staticmethod
def get_all():
return session.query(Admin).all()
from app.util.flask_extended import Flask
# store private information in instance
app = Flask(__name__, instance_relative_config=True, static_folder='static', template_folder='templates')
# Load default templates
app.config.from_object('app.default_config.DevelopmentConfig')
app.config.from_yaml('clients.yml', silent=True)
app.config.from_yaml('secret_stuff.yml', silent=True)
# Start the index service
if app.config['ENABLE_SEARCH']:
from whooshalchemy import IndexService
si = IndexService(config=app.config)
# Configure login page
lm = LoginManager()
lm.init_app(app)
lm.login_view = 'user.login'
# Render UTC dates based on browser settings
moment = Moment(app) # Also nice jquery and moment import
# Import base view
from . import view
def _index_tables(self):
"""
Index the needed tables according to the current session
:return:
"""
if self.session and self.tables and len(self.get_tables()) != 0:
logging.debug(inspect.stack()[0][3], "Executing index service")
# Registering index session
index_service = IndexService(session=self.session)
# Registering tables
if self.__class__.__name__ == 'ARPostORM':
# Registering AR tables
index_service.register_class(self.tables.Location)
index_service.register_class(self.tables.ExecutedAction)
index_service.register_class(self.tables.ExecutedActivity)
index_service.register_class(self.tables.PayloadValue)
index_service.register_class(self.tables.UserInRole)
index_service.register_class(self.tables.UserAction)
else:
# Registering SR tables
index_service.register_class(self.tables.VariationMeasureValue)
index_service.register_class(self.tables.NumericIndicatorValue)
index_service.register_class(self.tables.GeriatricFactorValue)
index_service.register_class(self.tables.CareProfile)
class DB:
def __init__(self,
*,
app,
database_uri='sqlite:///test.db',
index_path='.',
**args):
self.app = app
self.database_uri = database_uri
self.index_path = index_path
configure_mappers()
self.engine = create_engine(self.database_uri)
self.Session = sessionmaker(self.engine,
autoflush=True,
expire_on_commit=True)
self.session = self.Session()
Base.metadata.create_all(self.engine)
self.index = IndexService(whoosh_base=self.index_path,
session=self.session)
self.index.register_class(Article)
def list_articles(self):
with self.session_scope() as session:
return session.query(Article).all()
def delete_article(self, article):
with self.session_scope() as session:
session.delete(article)
self.app.updated()
def save(self, id, content, title):
with self.session_scope() as session:
article = session.query(Article).filter(Article.id == id).first()
article.content = content
if article.title != self.app.mainpage:
article.title = title
self.app.updated()
return article
def new(self, title):
with self.session_scope() as session:
article = Article()
article.title = title
article.content = "Write some content"
session.add(article)
self.app.updated()
return article
def exists(self, title):
with self.session_scope() as session:
return session.query(Article).filter(
Article.title == title).scalar() is not None
@contextmanager
def session_scope(self):
session = self.Session()
try:
yield session
session.commit()
except (SQLAlchemyError, DBAPIError) as e:
session.rollback()
raise
finally:
pass