def execute_sql(self, sql, params=None, require_commit=True): """ Function to re-connect to server if it disconnects """ try: return MySQLDatabase.execute_sql(self, sql, params, require_commit) except OperationalError as e: # If we are recovering, re-raise if self.__recovering: raise # Check for "SQL server has gone away" if e.args[0] == 2006: # Reconnect self.close() self.connect() # Retry self.__recovering = True try: ans = MySQLDatabase.execute_sql(self, sql, params, require_commit) self.__recovering = False return ans except OperationalError as e: self.__recovering = False raise # Check for "Cannot connect to the SQL server" elif e.args[0] == 2002: pass
def prepare(self):
self.my_database = MySQLDatabase(host='127.0.0.1',
user='******',
passwd='123456',
database='task_manage')
self.my_database.connect()
return super(BaseHandler, self).prepare()
def do_one_task(task):
import peewee
from peewee import MySQLDatabase
db = MySQLDatabase(
task["stream"]["database"], **{
'host': connection["host"],
'password': connection["passwd"],
'port': connection["port"],
'user': connection["user"]
})
pk = task["stream"].get("pk")
if not pk:
pk = {"field": "id", "type": "int"}
class MyModel(peewee.Model):
_pk = {
"char": peewee.CharField(primary_key=True),
"int": peewee.IntegerField(primary_key=True)
}[pk["type"]]
class Meta:
database = db
setattr(MyModel, pk["field"], MyModel._pk)
# 替换 sql 中的 `?` 为上次执行 sql 语句的时间
md5 = hashlib.md5(config.__name__ + task["stream"]["database"] +
task["stream"]["sql"]).hexdigest()
start_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
last_start_time = r.get(md5)
if not last_start_time:
if task["stream"].get("init_time"):
last_start_time = task["stream"].get("init_time")
else:
last_start_time = start_time
query = MyModel.raw(task["stream"]["sql"].replace("?", "%s"),
(last_start_time, )).dicts().iterator()
for row in query:
for job in task["jobs"]:
event = {"action": "insert", "values": row}
watched = job.get("watched") or job.get("filters")
if watched:
func_name, kwargs = watched.items()[0]
func = getattr(custom_rowfilters, func_name,
None) or getattr(row_filters, func_name)
is_ok = func(event, **kwargs)
if not is_ok:
continue
rows = do_pipeline(job["pipeline"], event["values"])
to_dest.make_docs(job["dest"], rows)
if len(to_dest.docs) >= to_dest.bulk_size:
to_dest.upload_docs()
db.close()
to_dest.upload_docs()
r.set(md5, start_time)
def populate_db(mysql_config: Dict[str, str],
drop_exist: bool = False) -> None:
db = MySQLDatabase(**mysql_config)
db.bind(MODELS, bind_refs=False, bind_backrefs=False)
db.connect()
if drop_exist:
db.drop_tables(MODELS)
db.create_tables(MODELS)
def __connect(self):
self.oDbBase = MySQLDatabase(
'chatterbot', **{
'host': '192.168.241.45',
'password': '******',
'port': 3306,
'user': '******',
'charset': 'utf8'
})
def connect(self):
db = MySQLDatabase('hlook',
host=self.args.host,
port=self.args.port,
user=self.args.username,
passwd=self.args.password)
db.connect()
databaseProxy.initialize(db)
print('Connection opened.')
return db
def setcache(username):
db = MySQLDatabase(app.config['DATABASE'], host = app.config['DATABASEHOST'], user = app.config['USERNAME'], passwd = app.config['PASSWORD'])
cur = db.get_cursor()
#rsslist = db.execute('select name, xmlurl from rsslist where user="******"')
#rsslist = rsslist.fetchall()
cur.execute('select name, xmlurl from rsslist where user="******"')
rsslist = cur.fetchall()
rsslist = formatrss(rsslist)
cache.set('rsslist', rsslist, timeout = 60 * 60 * 24)
return rsslist
def connect():
db = MySQLDatabase(
host='localhost',
user='******',
password='******',
database='guestbook'
)
db.connect()
return db
def check_mysql_up():
try:
db_handle = MySQLDatabase(database=MYSQL_DATABASE,
host=MYSQL_HOST,
port=MYSQL_PORT,
user=MYSQL_USER,
password=MYSQL_PASSWORD)
db_handle.connect()
return True
except OperationalError as e:
return False
def __init__(self, srv, info):
db_host = srv.cfg.db_host
db_port = srv.cfg.db_port
db_user = srv.cfg.db_user
db_passwd = srv.cfg.db_pass
self.database = MySQLDatabase(
info, **{
'host': db_host,
'password': db_passwd,
'user': db_user
})
def handle_init_stream(config):
connection = config.CONNECTION
to_dest = ToDest(config)
for task in config.TASKS:
import peewee
from peewee import MySQLDatabase
db = MySQLDatabase(
task["stream"]["database"], **{
'host': connection["host"],
'password': connection["passwd"],
'port': connection["port"],
'user': connection["user"]
})
pk = task["stream"].get("pk")
if not pk:
pk = {"field": "id", "type": "int"}
class MyModel(peewee.Model):
_pk = {
"char": peewee.CharField(primary_key=True),
"int": peewee.IntegerField(primary_key=True)
}[pk["type"]]
class Meta:
database = db
setattr(MyModel, pk["field"], MyModel._pk)
query = MyModel.raw(task["stream"]["sql"]).dicts().iterator()
for row in query:
for job in task["jobs"]:
event = {"action": "insert", "values": row}
if event["action"] not in job["actions"]:
continue
watched = job.get("watched")
if watched:
func_name, kwargs = watched.items()[0]
func = getattr(custom_rowfilters, func_name,
None) or getattr(row_filters, func_name)
is_ok = func(event, **kwargs)
if not is_ok:
continue
rows = do_pipeline(job["pipeline"], event["values"])
to_dest.make_docs(job["dest"], rows)
if len(to_dest.docs) >= to_dest.bulk_size:
to_dest.upload_docs()
db.close()
to_dest.upload_docs()
def init(self, database, **connect_kwargs):
"""
Override the initialization so we add extra variables
"""
# Call superclass
MySQLDatabase.init(self, database, **connect_kwargs)
# Get logger
self.logger = logging.getLogger("MySQL")
# Setup recovery flag
self.__recovering = False
class ModelBase(object):
def __init__(self):
self.__connect()
def __connect(self):
self.oDbBase = MySQLDatabase(
'chatterbot', **{
'host': '192.168.241.45',
'password': '******',
'port': 3306,
'user': '******',
'charset': 'utf8'
})
def query(self, sql):
'''
input : sql
output : data from table
'''
if not self.oDbBase:
self.__connect()
data_list = []
result = self.oDbBase.execute_sql(sql)
for array in result:
data_map = {}
j = 0
for value in array:
if type(value) == unicode:
value = value.encode('utf-8')
data_map[result.description[j][0].encode('utf-8')] = value
j += 1
data_list.append(data_map)
return data_list
def operate(self, sSql):
'''
input : sSql
ret : bool True->success False->failure
'''
if not self.oDbBase:
self.__connect()
bRet = self.oDbBase.execute_sql(sSql)
return bRet
def create_database_connection():
"""Creates a connection to a MySQLDatabase using the parmeters specified
in the setting file"""
try:
db = MySQLDatabase(
settings.DATABASE,
user=settings.USERNAME,
passwd=settings.PASSWORD,
host=settings.HOST
)
db.connect()
except Exception as e:
print "Failed to connect to database", e
return db
def get_system_database():
"""Returns connection to database received from Config"""
database_type = Config.DATABASE_TYPE.lower()
logger.debug("Connection to %s database (%s)", database_type,
Config.DATABASE_NAME)
if database_type == "mysql":
port = Config.DATABASE_PORT or 3306
return MySQLDatabase(Config.DATABASE_NAME,
host=Config.DATABASE_HOST,
user=Config.DATABASE_USER,
password=Config.DATABASE_PASSWORD,
port=port,
charset="utf8mb4")
elif database_type == "postgres":
port = Config.DATABASE_PORT or 5432
return PostgresqlDatabase(Config.DATABASE_NAME,
host=Config.DATABASE_HOST,
user=Config.DATABASE_USER,
password=Config.DATABASE_PASSWORD,
port=port)
elif database_type == "sqlite":
return SqliteDatabase(Config.DATABASE_NAME)
else:
raise DatabaseException(
"Supports sqlite, postgres or mysql(mariadb) databases, not '{}'".
format(database_type))
class ConfigBase(object):
"""MySQL Database handler"""
DATABASE = MySQLDatabase(
'DATABASE', **{
'charset': 'utf8',
'use_unicode': True,
'user': '******',
'passwd': 'PASSWORD'
})
SECRET_KEY = "SECRET KEY"
ENV = 'development'
# Spotify API
ID = "CLIENT ID"
SECRET = "SECRET CLIENT ID"
# Genius API
TOKEN = "TOKEN"
LEXICON_SADNESS = [
line.strip() for line in open("./lexicon/sadness.txt", 'r')
]
LEXICON_FEAR = [line.strip() for line in open("./lexicon/fear.txt", 'r')]
LEXICON_ANGER = [line.strip() for line in open("./lexicon/anger.txt", 'r')]
STOP_WORDS = [
line.strip() for line in open("./lexicon/stopwords.txt", 'r')
]
def setup(cls):
"""sets up the SQL System"""
mode = CONFIG["database"]["mode"].value.lower()
if mode == "sqlite3":
database = SqliteExtDatabase(
PROGRAMCONFIGLOCATION + "/Tackem.db",
pragmas={
"journal_mode": "wal",
"foreign_keys": 0
},
)
elif mode == "mysql":
database = MySQLDatabase(
CONFIG["database"]["database"].value,
user=CONFIG["database"]["username"].value,
password=CONFIG["database"]["password"].value,
host=CONFIG["database"]["host"].value,
port=CONFIG["database"]["port"].value,
)
else:
print(mode)
cls.__db.initialize(database)
if mode == "sqlite3":
cls.__migrator = SqliteMigrator(cls.__db)
elif mode == "mysql":
cls.__migrator = MySQLMigrator(cls.__db)
TableVersion.create_table()
class Meta:
database = MySQLDatabase(
'sku_db',
host='account-manager-stage.app.eng.rdu2.redhat.com',
password='******',
user='******')
db_table = 'sku_attributes_all'
def gendb(type, path, host, port, db, user, passwd, tablename=config.DB_TABLE):
if type == 'sqlite':
db = SqliteDatabase(config.DB_PATH)
elif type == 'mysql':
if host is None or port is None or user is None or passwd is None:
return config.failed
db = MySQLDatabase(db, host=host, port=port, user=user, passwd=passwd)
elif type == 'postgresql':
if host is None or port is None or user is None or passwd is None:
return config.failed
db = PostgresqlDatabase(db,
host=host,
port=port,
user=user,
password=passwd)
else:
logging.error('Tipo de base de datos no soportado o inválido: %s',
type)
return config.failed
class table(BaseModel):
class Meta:
database = db
table._meta.db_table = tablename
return (db, table)
def db(self):
global _MySQLDatabase
_MySQLDatabase = MySQLDatabase(self.conf["db"],
host=self.conf["host"],
user=self.conf["user"],
passwd=self.conf["passwd"])
return _MySQLDatabase
class Setting(object):
#path
rss_icon_path = ""
main_log_path = ''
#public config
log_enable = True
debuging = True
persian_timezone_seconds = 0
home_page_news = 0
crawl_interval = 0
log_level=0
#shared objects
sid = None
log = LogUtil(enable=False)
db = {}
database = MySQLDatabase(None)
def __init__(self, params):
'''
Constructor
'''
pass
def init_mysql(settings: dict):
keys = {"database", "user", "password", "host", "port"}
settings = {k: v for k, v in settings.items() if k in keys}
global MySQL_con
MySQL_con = pymysql.connect(**settings)
db = MySQLDatabase(**settings)
return db
def init_db():
global DB
DB = MySQLDatabase(host="ofmc.me",
port=3306,
user="******",
passwd="password",
database="cs336")
def get_mysql_db():
logger.info('Connecting to Mysql database..')
return MySQLDatabase(CONFIG.MYSQL_DB,
user=CONFIG.MYSQL_USER,
password=CONFIG.MYSQL_PASSWORD,
host=CONFIG.MYSQL_SERVER,
port=int(CONFIG.MYSQL_PROT))
class Meta:
database = MySQLDatabase(testdata_db_cfg['db'],
user=testdata_db_cfg['username'],
password=testdata_db_cfg['password'],
host=testdata_db_cfg['host'],
port=3306)
table_name = 'tb_testdata'
def doSQL(n, u, h, a, g):
db = MySQLDatabase("asm_main", host=host, port=3306, user=user, passwd=passwd)
db.connect()
cur = db.get_cursor()
nick = n
user = u
host = h
account = a
gecos = g
escape = db.get_conn().escape_string
sql = "SELECT * FROM actionlog WHERE "
search = []
if nick:
search.append("nick LIKE '{nick}'".format(nick=escape(nick)))
if user:
search.append("user LIKE '{user}'".format(user=escape(user)))
if host:
search.append("host LIKE '{host}'".format(host=escape(host)))
if account:
search.append("account LIKE '{account}'".format(account=escape(account)))
if gecos:
search.append("gecos LIKE '{gecos}'".format(gecos=escape(gecos)))
sql = sql+" OR ".join(search)
l = cur.execute(sql)
output = ""
for row in cur.fetchall():
output+= "<tr>"
output+= "<td>#{}</td>".format(row[0]) # id
output+= "<td>{}</td>".format(row[1]) # date
output+= "<td>{}<span class=\"userhost\">!{}@{}</span></td>".format(row[5],row[6],row[7]) # nick!ident@host
output+= "<td>received {}".format(row[2]) # event
# (11, datetime.datetime(2016, 2, 21, 20, 25, 45), 'quiet', None, '##doge', 'doge', '~doge',
# 'antispammeta/suchmeta/botters.doge', None, 'doge', 'doge', 'falco-devel', '~falco',
# 'botters/doge/bot/falco', "nathan's bot", 'falco')
if row[11]:
output+= " from {}<span class=\"userhost\">{}{}</span></td>".format(
row[11], "!"+row[12] if row[12] else "", "@"+row[13] if row[13] else "") # who it was from
elif not row[11]:
output+= "</td>"
output+= "<td>{}</td>".format(row[4] if row[4] else "") # channel
output+= "<td>{}</td>".format(row[3] if row[3] else "") # reason
output+= "</tr>"
return output
def addrss():
if session.has_key('username'):
username = session['username']
else:
return 'no login'
db = MySQLDatabase(app.config['DATABASE'], host = app.config['DATABASEHOST'], user = app.config['USERNAME'], passwd = app.config['PASSWORD'])
cur = db.get_cursor()
cur.execute('select count(id) from rsslist')
count = cur.fetchall()
count = int(count[0][0])
cmd = 'insert into rsslist (id, name, xmlurl, htmlurl, user) values(' + str(count) + ',' + '"' + request.form['name'] + '"' + ',' + '"'+request.form['rssxml'] +'"'+ ',' + '"'+request.form['rsshtml'] +'"'+ ',' + '"'+username +'"'+ ')'
cur.execute(cmd)
#cur.commit()
db.commit()
setcache(username)
return 'add finish'
class BaseHandler(tornado.web.RequestHandler):
'''
所有Handler的父类
定义数据库的连接与关闭
'''
def prepare(self):
self.my_database = MySQLDatabase(host='127.0.0.1',
user='******',
passwd='123456',
database='task_manage')
self.my_database.connect()
return super(BaseHandler, self).prepare()
def on_finish(self):
if not self.my_database.is_closed():
self.my_database.close()
return super(BaseHandler, self).on_finish()
def get_mysql():
db = MySQLDatabase('my_app',
user='******',
password='******',
host='localhost',
port=3306)
return db
def create_client(self, **kwargs):
client = MySQLDatabase(self.mysql_config.dbname,
host=self.mysql_config.host,
user=self.mysql_config.username,
port=self.mysql_config.port,
passwd=self.mysql_config.password,
**kwargs)
logger.debug("create mysql client:{}".format(client))
return client
def create_db_interface(**mysql_kwargs):
"""Create MySQL database interface using given connection parameters. `mysql_kwargs`
are forwarded to `pymysql.connect`.
Configure primary keys to be unsigned integer.
"""
return MySQLDatabase(**mysql_kwargs,
field_types={
"AUTO": "INTEGER UNSIGNED AUTO_INCREMENT"
})
class Meta(object):
"""表配置信息
"""
database = MySQLDatabase(database="xyz",
host="127.0.0.1",
password="******",
user="******",
port=3306)
db_table = "tb_raw"
def Conn(self):
return MySQLDatabase(
host=cfg.get("mysql", "server")
,port=int(cfg.get("mysql", "port"))
,database=cfg.get("mysql", "database")
, user=cfg.get("mysql", "user")
, passwd=cfg.get("mysql", "passwd")
, charset='utf8'
)
class ConfigBase(object):
"""MySQL Database handler"""
DATABASE = MySQLDatabase(
'<database>', **{
'charset': 'utf8',
'use_unicode': True,
'user': '******',
'passwd': '<password>'
})
def create_database(instrument_host, credentials):
if not credentials:
username, password = get_credentials(CREDS_GROUP, "ExpDatabaseWrite")
else:
username, password = credentials
return MySQLDatabase("exp_data",
user=username,
password=password,
host=instrument_host)
def __init_mysql(dbconf):
db = MySQLDatabase(database=dbconf['name'],
host=dbconf['host'],
port=dbconf['port'],
user=dbconf['user'],
passwd=dbconf['password'])
DbConnection.__instance.val = db
return db
import time
from HTMLParser import HTMLParser
from peewee import MySQLDatabase, Field, Model, CharField, DecimalField, DateTimeField, IntegerField, BigIntegerField
from credentials import MYSQL_USER, MYSQL_PASS, MYSQL_HOST
DB_NAME = 'twitter'
FILTER_LEVEL = {None: None, 'none': 0, 'low': 1, 'medium': 2, 'high': 3}
html_parser = HTMLParser()
MySQLDatabase.register_fields({'TINYINT': 'TINYINT'})
database = MySQLDatabase(DB_NAME, host=MYSQL_HOST, port=3306, user=MYSQL_USER, passwd=MYSQL_PASS)
# Custom Field definitions
class TinyIntField(Field):
db_field = 'TINYINT'
# Model definitions
class BaseModel(Model):
class Meta:
database = database
# Should be ~400 bytes max
class Tweet(BaseModel):
import student_info
import student_behavior
import student_punish
import student_relative
import student_resume
import student_reward
import course
import term
import score
if __name__ == '__main__':
Config.load('../config/server.json')
database = MySQLDatabase(Config.mysql_db, autocommit=False, **{'threadlocals': True,
'host': Config.mysql_host,
'password': Config.mysql_pwd,
'port': Config.mysql_port,
'user': Config.mysql_user})
# Connect to our database.
database.begin()
# Create the tables.
database.create_tables([
# grade.Grade,
major.Major,
student_info.StudentInfo,
student_behavior.StudentBehavior,
student_punish.StudentPunish,
student_relative.StudentRelative,
student_resume.StudentResume,
import json
import config
from peewee import Model, CharField, DateTimeField
from peewee import SqliteDatabase, MySQLDatabase
db = None
if config.DB_TYPE == 'mysql':
db = MySQLDatabase(config.DB_NAME,
user = config.DB_USER,
password = config.DB_PASSWORD,
host = config.DB_HOST,
port = config.DB_PORT,
charset='utf8mb4')
if config.DB_TYPE == 'sqlite':
db = SqliteDatabase(config.DB_NAME)
class JsonModel(Model):
def jsonify(self):
r = {}
for k in self._data.keys():
try:
r[k] = str(getattr(self, k))
except:
r[k] = json.dumps(getattr(self, k))
return str(r)
class User(JsonModel):
class Meta:
database = db
name = CharField(unique = True)
run_mode = os.environ.get('BMWLOG_MODE', 'development')
config_object = '%sConfig' % run_mode.capitalize()
try:
config_module = getattr(config, config_object)
except AttributeError:
print('Invalid config or environment variable. '
'No such configuration: %s' % config_object,
file=sys.stderr)
sys.exit(1)
return config_module
config = load_config()
db = MySQLDatabase(config.DB_NAME,
host=config.DB_HOST, port=config.DB_PORT,
user=config.DB_USER, password=config.DB_PASS)
db.get_conn().ping(True)
app = Bottle()
from plugins.flash import FlashPlugin
from plugins.login_manager import LoginManager
from plugins.logging_plugin import LoggingPlugin
app.install(FlashPlugin(secret=config.SECRET_KEY))
app.install(LoginManager(secret=config.SECRET_KEY))
app.install(LoggingPlugin())
env = Environment(loader=PackageLoader('app', '../templates'))
env.globals['app'] = app
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import _env # noqa
import hashlib
from config import MYSQL
from peewee import Model, MySQLDatabase
from playhouse.shortcuts import model_to_dict, dict_to_model
from sqlalchemy import create_engine
db = MySQLDatabase(MYSQL.DB, user=MYSQL.USER, password=MYSQL.PWD, host=MYSQL.HOST)
class Base(Model):
def to_dict(self):
return model_to_dict(self)
@classmethod
def from_dict(cls, d):
return dict_to_model(cls, d) if d else None
@classmethod
def get_conn(cls):
engine = create_engine('mysql://{user}:{passwd}@{host}/{db}?charset=utf8'.format(**dict(
host=MYSQL.HOST,
user=MYSQL.USER,
passwd=MYSQL.PWD,
db=MYSQL.DB
)))
def __init__(self):
self.database = MySQLDatabase("test", host="", port=3306, user="",
passwd="")
def load_database(self):
self.db = self.connect_kwargs.pop("db")
self.database = MySQLDatabase(self.db, **self.connect_kwargs)
self.database.field_overrides.update({"enum": "enum"}) # 增加枚举类型
import collections
import events
import getinput
import json
import operator
import peewee
import re
import sys
from . import constants
from . import definition
from . import ehphp
from . import helpers
from . import settings
database = MySQLDatabase(settings.DATABASE, user=settings.USER, passwd=settings.PASSWD, charset='utf8')
database.get_conn().ping(True)
class BaseModel(Model):
creation_event = None
save_event = None
class Meta(object):
database = database
@classmethod
def create(cls, *args, **kwargs):
result = super().create(*args, **kwargs)
if cls.creation_event is not None:
cls.creation_event.trigger(result)
#!/usr/bin/env python
# encoding: utf-8
from peewee import (MySQLDatabase, Model, ForeignKeyField,
TextField, BigIntegerField, DateTimeField)
import settings
database = MySQLDatabase(settings.DATABASE,
user=settings.USER,
password=settings.PASSWORD)
database.connect()
class BaseModel(Model):
class Meta:
database = database
class Category(BaseModel):
name = TextField()
class Torrent(BaseModel):
category = ForeignKeyField(Category)
title = TextField()
size = BigIntegerField()
magnet = TextField()
hash = TextField()
nfo = TextField()
parser.add_argument('--daemon', type=bool, default=False,
help='Start in daemon mode which notifies to rekall')
parser.add_argument('--concept', type=str,
help='Concept to store for rekall')
parser.add_argument('--rekall', type=bool, default=False,
help='Print text and increment rekall')
parser.add_argument('--remove', type=bool, default=False,
help='Remove a concept')
parser.add_argument('--printall', type=bool, default=False,
help='Print everything in the database')
config = ConfigParser.ConfigParser()
config.read(os.path.expanduser('~/.rekall'))
args = parser.parse_args()
db = MySQLDatabase("rekall", host=config.get("mysql", "host"), user=config.get("mysql", "user"), passwd=config.get("mysql", "password"), charset="utf8", use_unicode=True)
db.connect()
class BaseModel(Model):
class Meta:
database = db
class Concept(BaseModel):
name = CharField()
text = TextField(default="# Explanation / Analogy / Like I'm Five" )
last_rekall = DateTimeField(default=datetime.datetime.now)
rekalls = IntegerField(default=0)
def next_rekall(self):
return self.last_rekall + datetime.timedelta(seconds=(INTERVAL_SECONDS ^ self.rekalls))
def get_conn(self):
con = MySQLDatabase.get_conn(self)
con.ping()
return con
class Database(object):
"""db封装,自动查找数据库
"""
def __init__(self, **connect_kwargs):
self.connect_kwargs = connect_kwargs
self.load_database()
self.Model = self.get_model_class()
def load_database(self):
self.db = self.connect_kwargs.pop("db")
self.database = MySQLDatabase(self.db, **self.connect_kwargs)
self.database.field_overrides.update({"enum": "enum"}) # 增加枚举类型
def get_model_class(self):
"""获取基类model
"""
class BaseModel(_Model):
"""BaseModel的封装
"""
class Meta(object):
"""元类
"""
database = self.database
@classmethod
def one(cls, *query, **kwargs):
"""获取单条数据
Retruns:
返回单条数据不存在则返回None
"""
try:
return cls.get(*query, **kwargs)
except DoesNotExist:
return None
def delete_instance(self, *args, **kwargs):
"""如果deleted字段存在自动使用逻辑删除
"""
if "deleted" in self._meta.fields:
setattr(self, "deleted", "1")
super(BaseModel, self).save()
else:
super(BaseModel, self).delete_instance(*args, **kwargs)
def __hash__(self):
"""提供hash支持
"""
return hash(self.id)
return BaseModel
def connect(self):
"""主从建立连接,如果连接关闭重试
"""
i = 0
while i < 4:
try:
if self.database.is_closed():
self.database.get_conn().ping(True)
break
except OperationalError:
self.close()
i = i + 1
def close(self):
"""关闭连接
"""
try:
self.database.close()
except:
pass
def initApp(self, **kwargs):
# Initialize Flask
self.__flask = Flask(import_name = self.__appname,
template_folder = self.__settings.FLASK_TEMPLATES_DIR,
static_folder = self.__settings.FLASK_STATIC_DIR,
**kwargs)
self.__flask.secret_key = self.__settings.SECRET_KEY
# Init G
from . import g
setattr(g, '__cur_phial', self)
setattr(g, '__settings', self.__settings)
# WSGI support
self.__flask.wsgi_app = ProxyFix(self.__flask.wsgi_app)
# Fix UTF-8 issue on python 2.x
if sys.version_info[0] < 3:
reload(sys)
sys.setdefaultencoding('utf-8')
# Add regex on Flask URL map
self.__flask.url_map.converters['regex'] = FlaskURLMapRegexConverter
# Init Peewee
from .peewee_tools import base_model as peewee_bm
if peewee_bm.BaseModel is not None and getattr(self.__settings, 'DATABASE_CONN_STRING', None) is not None:
if self.__settings.DATABASE_CONN_STRING.startswith('mysql://') is True:
# Load lib
try:
from peewee import MySQLDatabase
except ImportError:
print('[Phial][ERRO] MySQL driver is not installed (pip install MySQL-python or PyMySQL)')
sys.exit(1)
peewee_bm.gl_database_type = 1
hdl = MySQLDatabase(None)
# Override Peewee init to work with connstring
from .peewee_tools import peewee_overrided_database_init
from peewee import Database
funcType = type(Database.init)
hdl.init = funcType(peewee_overrided_database_init, hdl, Database)
# Register to Proxy
peewee_bm.gl_database.initialize(hdl)
elif self.__settings.DATABASE_CONN_STRING.startswith('sqlite') is True:
# Load lib
try:
from peewee import SqliteDatabase
except ImportError:
print('[Phial][ERRO] sqlite3 is not installed')
sys.exit(1)
peewee_bm.gl_database_type = 2
hdl = SqliteDatabase(None)
# Override Peewee init to work with connstring
from .peewee_tools import peewee_overrided_database_init
from peewee import Database
funcType = type(Database.init)
hdl.init = funcType(peewee_overrided_database_init, hdl, Database)
# Register to Proxy
peewee_bm.gl_database.initialize(hdl)
else:
print('[Phial][ERRO] Database backend not supported')
sys.exit(1)
# Init Database
peewee_bm.gl_database.init(self.__settings.DATABASE_CONN_STRING)
# Session handler
if (getattr(self.__settings, 'FLASK_SESSION_HANDLER', None) and getattr(self.__settings, 'FLASK_SESSION_HANDLER_CFG', None)) is not None:
try:
mod_path = self.__settings.FLASK_SESSION_HANDLER.rsplit('.', 1)[0]
class_name = self.__settings.FLASK_SESSION_HANDLER.split('.')[-1]
hdl = __import__(mod_path, fromlist=[mod_path.split('.')[-1]])
sess_handler = getattr(hdl, class_name)
try:
self.__flask.session_interface = sess_handler(**self.__settings.FLASK_SESSION_HANDLER_CFG)
except:
print('[Phial][ERRO] Session handler is misconfigured')
sys.exit(1)
except (ImportError, AttributeError) as e:
print("[Phial][ERRO] Can't load session backend \"%s\": %s" % (self.__settings.FLASK_SESSION_HANDLER, e))
sys.exit(1)
# Load user-defined routes
try:
url_map = __import__('%s.routes' % self.__appname, fromlist=['routes'])
for u in getattr(url_map, 'routes', []):
try:
try:
self.__flask.add_url_rule(u[0], view_func=u[1], methods=u[2], defaults=u[3])
except IndexError:
self.__flask.add_url_rule(u[0], view_func=u[1], methods=u[2])
except AttributeError:
print('[Phial][WARN] Ignoring bad route entry: %s' % str(u))
for u in getattr(url_map, 'errors', []):
self.__flask.error_handler_spec[None][u[0]] = u[1]
except ImportError:
try:
from cStringIO import StringIO
except ImportError:
from StringIO import StringIO
import traceback
stream_output = StringIO()
traceback.print_exc(file=stream_output)
print('[Phial][ERRO] File "%s.routes" does not exist or contain some errors...' % (self.__appname))
print(stream_output.getvalue())
sys.exit(1)
# Set default welcome page if user-defined routes map is empty
if len(self.__flask.url_map._rules) < 2:
from os.path import dirname, join
self.__flask.template_folder = join(dirname(__file__), 'assets/templates')
self.__flask.static_folder = join(dirname(__file__), 'assets/static')
self.__flask.add_url_rule('/', view_func=self.__default_welcome, methods=['GET'])
# If application DEBUG==True -> redefine 404 error page
if self.__settings.FLASK_DEBUG is True:
self.__flask.error_handler_spec[None][404] = self.__default_debug_404
# Jinja2 DEBUG
self.__flask.jinja_env.globals.update({'DEBUG' : self.__settings.FLASK_DEBUG})
# Jinja2 built-in extensions
self.__flask.jinja_env.add_extension('jinja2.ext.do')
self.__flask.jinja_env.add_extension('jinja2.ext.with_')
self.__flask.jinja_env.add_extension('jinja2.ext.i18n')
self.__flask.jinja_env.add_extension('phial.jinja_tools.ext.SelectiveHTMLCompress')
self.__flask.jinja_env.add_extension('phial.jinja_tools.ext.DatetimeExtension')
# Jinja2 i18n callables
self.__flask.jinja_env.install_gettext_callables(
lambda x: jinja_get_translation_engine().ugettext(x),
lambda s, p, n: jinja_get_translation_engine().ungettext(s, p, n),
newstyle = True)
# Jinja2 user-defined extensions
mod_path = getattr(self.__settings, 'JINJA_CUSTOM_EXTENSION_DIR', '%s.phial.jinja.extension' % self.__appname)
try:
hdl = __import__(mod_path, fromlist=[mod_path.split('.')[-1]])
for im in getattr(hdl, '__all__', []):
try:
self.__flask.jinja_env.add_extension('%s.%s' % (mod_path, im))
except AttributeError:
print('[Phial][WARN] Extension "%s.%s" does not exist' % (mod_path, im))
except ImportError:
pass
# Jinja2 user-defined filters
mod_path = getattr(self.__settings, 'JINJA_CUSTOM_FILTER_DIR', '%s.phial.jinja.filter' % self.__appname)
try:
hdl = __import__(mod_path, fromlist=[mod_path.split('.')[-1]])
for im in getattr(hdl, '__all__', []):
item_hdl = getattr(hdl, im, None)
if item_hdl is not None:
self.__flask.jinja_env.filters.update({im : item_hdl})
else:
print('[Phial][WARN] Filter "%s.%s" does not exist' % (mod_path, im))
except ImportError:
pass
# Jinja2 user-defined functions
mod_path = getattr(self.__settings, 'JINJA_CUSTOM_FUNCTION_DIR', '%s.phial.jinja.function' % self.__appname)
try:
hdl = __import__(mod_path, fromlist=[mod_path.split('.')[-1]])
for im in getattr(hdl, '__all__', []):
item_hdl = getattr(hdl, im, None)
if item_hdl is not None:
self.__flask.jinja_env.globals.update({im : item_hdl})
else:
print('[Phial][WARN] Function "%s.%s" does not exist' % (mod_path, im))
except ImportError:
pass
# Register before & after callback
from .flask_tools.callback import flask_before_url_remove_trailing_slash, flask_before_i18n_set_user_language
from .flask_tools.callback import flask_before_peewee_connect, flask_after_peewee_close
self.__flask.before_request(flask_before_url_remove_trailing_slash)
self.__flask.before_request(flask_before_peewee_connect)
self.__flask.before_request(flask_before_i18n_set_user_language)
self.__flask.after_request(flask_after_peewee_close)
for im in getattr(self.__settings, 'FLASK_REQUEST_CALLBACK', []):
try:
mod_path = im[1].rsplit('.', 1)[0]
class_name = im[1].split('.')[-1]
hdl = __import__(mod_path, fromlist=[mod_path.split('.')[-1]])
call_hdl = getattr(hdl, class_name)
if im[0] == 'before':
self.__flask.before_request(call_hdl)
elif im[0] == 'after':
self.__flask.after_request(call_hdl)
except (ImportError, AttributeError) as e:
print("[Phial][WARN] Can't load flask callback \"%s\": %s" % (im[1], e))
except IndexError:
print('[Phial][ERRO] FLASK_REQUEST_CALLBACK is misconfigured: %s' % e)
sys.exit(1)
# Register Celery (if lib is installed) and settings OK
if getattr(self.__settings, 'CELERY_BROKER_URL', None) is not None:
try:
from celery import Celery
except ImportError:
print('[Phial][ERRO] Celery is not installed (pip install -U celery)')
sys.exit(1)
else:
self.__flask.config.update(CELERY_BROKER_URL = self.__settings.CELERY_BROKER_URL,
CELERY_RESULT_BACKEND = getattr(self.__settings, 'CELERY_RESULT_BACKEND'),
CELERY_ACCEPT_CONTENT = getattr(self.__settings, 'CELERY_ACCEPT_CONTENT'))
self.__celery = Celery(main = self.__flask.import_name,
broker = self.__flask.config['CELERY_BROKER_URL'],
backend = self.__flask.config['CELERY_RESULT_BACKEND'])
self.__celery.config_from_object(self.__flask.config)
# Override Task to set the Flask app context
TaskBase = self.__celery.Task
class ContextTask(TaskBase):
abstract = True
def __call__(self, *args, **kwargs):
from . import current_phial
with current_phial.flaskapp.app_context():
return TaskBase.__call__(self, *args, **kwargs)
setattr(self.__celery, 'Task', ContextTask)
# Register to globals
setattr(g, '__celery', self.__celery)
# Register tasks
mod_path = getattr(self.__settings, 'CELERY_CUSTOM_TASKS_DIR', '%s.celery' % self.__appname)
try:
hdl = __import__(mod_path, fromlist=[mod_path.split('.')[-1]])
for im in getattr(hdl, '__all__', []):
item_hdl = getattr(hdl, im, None)
if item_hdl is not None:
self.__celery.tasks.register(item_hdl)
else:
print('[Phial][WARN] Async task "%s.%s" does not exist' % (mod_path, im))
except ImportError as e:
print("[Phial][ERRO] Can't load celery async tasks: %s" % e)
sys.exit(1)
# Register error handlers (RELEASE MODE ONLY)
if self.__settings.FLASK_DEBUG is False and len(self.__settings.ADMINS) > 0 and len(self.__settings.MAIL_USERNAME) > 0:
hMailHandler = SMTPHandler((self.__settings.MAIL_SMTP, self.__settings.MAIL_SMTP_PORT),
self.__settings.MAIL_USERNAME,
[a[1] for a in self.__settings.ADMINS],
self.__settings.LOGGING_MAIL_TITLE,
(self.__settings.MAIL_USERNAME, self.__settings.MAIL_PASSWORD))
if getattr(self.__settings, 'LOGGING_MAIL_TEMPLATE', None) is not None:
hMailHandler.setFormatter(Formatter(self.__settings.LOGGING_MAIL_TEMPLATE))
hMailHandler.setLevel(ERROR)
self.__flask.logger.addHandler(hMailHandler)
hFileHandler = RotatingFileHandler(self.__settings.LOGGING_FILE_FILENAME, 'a', self.__settings.LOGGING_FILE_MAXSIZE, self.__settings.LOGGING_FILE_NBROTATE)
if getattr(self.__settings, 'LOGGING_FILE_TEMPLATE', None) is not None:
hFileHandler.setFormatter(Formatter(self.__settings.LOGGING_FILE_TEMPLATE))
hFileHandler.setLevel(ERROR)
self.__flask.logger.addHandler(hFileHandler)
class Dao:
database = None
def __init__(self):
self.database = MySQLDatabase("test", host="", port=3306, user="",
passwd="")
def getRows(self):
"""sql = SELECT
a.nid,
a.title,
b.field_body_value,
concat('http://data.gov.tw/node/',a.nid),
h.field_resource_description_g_value,
e.field_resource_url_g_url,
g.name
FROM test.node a
inner join test.field_data_field_body b
on a.nid = b.entity_id
inner join test.field_data_field_dataset_status c
on a.nid = c.entity_id
inner join test.field_data_field_gov_resource d
on a.nid = d.entity_id
inner join test.field_data_field_resource_url_g e
on d.field_gov_resource_value = e.entity_id
inner join test.field_data_field_format_g f
on d.field_gov_resource_value = f.entity_id
inner join test.taxonomy_term_data g
on f.field_format_g_tid = g.tid
inner join test.field_revision_field_resource_description_g h
on d.field_gov_resource_value = h.entity_id
where 1=1
and c.field_dataset_status_value = '4'"""
sql = """SELECT
a.nid,
a.title,
b.field_body_value,
concat('http://data.gov.tw/node/',a.nid),
h.field_resource_description_g_value,
e.field_resource_url_g_url,
g.name
FROM test.node a
inner join test.field_data_field_body b
on a.nid = b.entity_id
inner join test.field_data_field_dataset_status c
on a.nid = c.entity_id
right join test.field_data_field_gov_resource d
on a.nid = d.entity_id
inner join test.field_data_field_resource_url_g e
on d.field_gov_resource_value = e.entity_id
inner join test.field_data_field_format_g f
on d.field_gov_resource_value = f.entity_id
inner join test.taxonomy_term_data g
on f.field_format_g_tid = g.tid
left join test.field_revision_field_resource_description_g h
on d.field_gov_resource_value = h.entity_id
where 1=1
and c.field_dataset_status_value = '4'"""
cursor = self.database.execute_sql(sql)
models = []
for row in cursor.fetchall():
model = DBModel(
nid=row[0],
title=row[1],
field_body_value=row[2],
link=row[3],
field_resource_description_g_value=row[4],
field_resource_url_g_url=row[5],
name=row[6],
)
models.append(model)
return models
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from os.path import realpath, join, dirname, abspath
from datetime import datetime
from peewee import MySQLDatabase, Model
from peewee import CharField, IntegerField, DateTimeField, TextField
from util import string_to_datetime
db = MySQLDatabase('cupels', user='******', charset='utf8mb4')
db.connect()
class NoTZDateTimeField(DateTimeField):
def db_value(self, value):
return value.replace(tzinfo=None)
class BaseModel(Model):
class Meta:
database = db
class Flight(BaseModel):
departure_port = CharField(max_length=10, null=True, index=True)
arrival_port = CharField(max_length=10, null=True, index=True)
company_code = CharField(max_length=5, null=True, index=True)
cabin = CharField(max_length=5, null=True)
ticket_price = IntegerField(null=True, index=True)
stay_day_min = CharField(max_length=5, null=True)
stay_day_max = CharField(max_length=5, null=True)
valid_date_from = NoTZDateTimeField(index=True)
valid_date_to = NoTZDateTimeField(index=True)
