def __init__(self):
self.dns = MySQL(host=DNS_HOST,
user=DNS_USER,
passwd=DNS_PASSWD,
port=DNS_PORT,
db=DNS_DB)
self.initCppLib()
def douyin_count():
douyin_dir = "/mnt/data/douyin_bak/account"
douyin_size = count(character, douyin_dir)
account_size = 0 if douyin_size == None else douyin_size
article_dir = "/mnt/data/douyin_bak/article"
article_size = count(character, article_dir)
article_size = 0 if article_size == None else article_size
update_dir = "/mnt/data/douyin_bak/update"
update_size = count(character, update_dir)
update_size = 0 if update_size == 0 else update_size
video_dir = "/mnt/data/douyin_bak/video"
video_size = video_count("mp4", video_dir)
video_size = 0 if video_dir == None else video_size
# 总量数据
total_data = account_size + article_size + update_size + video_size
count_result = Field.get_fields()
count_result['total_data'] = total_data
count_result['account_total'] = account_size
count_result['article_total'] = article_size
count_result['update_total'] = update_size
count_result['video_total'] = video_size
count_result['cycle_type'] = interval
mysql = MySQL(user=user, pwd=password, host=host, db=db, tb=douyin_tb)
mysql.update_data(count_result)
def get_check_item():
db = MySQL(config.dqc_mysql)
result = db.execute(const.CHECK_ITEM)
del db
item = {}
for rs in result:
item[rs['Chk_Proj_Cd']] = rs['Chk_Projid']
return item
def get_label_id(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(mask_const.MASK_LABLE, (table_id, ))
del db
if len(result) == 0:
raise exception.MaskException(
"table lable is non-exist. [table_id:%s]" % table_id)
return result[0]["Labelid"]
async def test_mysql_createConnection(self):
conn = MySQL()
await conn.create_connection({
'host': 'localhost',
'user': '******',
'password': '******'
})
await self.assertNotEqual(conn.connection(), None)
def get_field(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.FIELD_TABLE, (table_id, ))
del db
item = {}
for rs in result:
item[rs['Fld_Phys_Nm']] = rs['Fldid']
return item
def get_partition_path(table_id, partition_date):
db = MySQL(config.dqc_mysql)
result = db.execute(const.PARTITION_PATH, (table_id, partition_date))
del db
if len(result) == 0:
return None
else:
return result[0]["dp_path"]
def get_target_database(database, usage):
db = MySQL(config.dqc_mysql)
result = db.execute(const.TARGET_DATABASE, (database, usage))
del db
if len(result) == 0:
raise exception.DQCException(
"target database is non-exist. [database:%s]" % database)
return result[0]["db_phys_nm"]
def get_change_ddl(table_id):
flag = False
db = MySQL(config.dqc_mysql)
result = db.execute(mask_const.TABLE_CHANGE, (table_id, ))
if len(result) != 0:
flag = True
del db
return flag
def get_database_table(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.DB_TABLE, (table_id, ))
del db
if len(result) == 0:
raise exception.DQCException(
"database table is non-exist. [table_id:%s]" % table_id)
return result[0]["db_phys_nm"], result[0]["data_tbl_phys_nm"]
def get_partition_latest(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.MAX_PARTITION_DATE, (table_id, ))
del db
if result[0]["latest"] is None:
raise exception.DQCException(
"table partition is non-exist. [table_id:%s]" % table_id)
else:
return result[0]["latest"]
def get_mask_cmpu():
db = MySQL(config.dqc_mysql)
result = db.execute(mask_const.MASK_CMPU)
del db
mask_cmpu = {
rs["Data_Wash_Cmpu_Cd"]: rs["Data_Wash_Cmpuid"]
for rs in result
}
return mask_cmpu
def gethiveP(db):
try:
conn = MySQL(config.hivemeta)
partitions = conn.execute(const.getPSql.format(db))
except Exception:
logging.error('获取hive 分区元数据失败！')
print traceback.format_exc()
sys.exit(1)
del conn
return partitions
def gethiveC(db):
try:
conn = MySQL(config.hivemeta)
columns = conn.execute(const.getCSql.format(db))
except Exception:
logging.error('获取hive 字段元数据失败')
print traceback.format_exc()
sys.exit(1)
del conn
return columns
def getCT(db):
try:
conn = MySQL(config.washmeta)
tables = conn.execute(const.getCTSql.format(db))
except Exception:
logging.error('获取清洗库 表元数据 失败！')
print traceback.format_exc()
sys.exit(1)
del conn
return tables
def getCC(db):
try:
conn = MySQL(config.washmeta)
columns = conn.execute(const.getCCSql.format(db))
except Exception:
logging.error('获取清洗库 字段数据 失败！')
print traceback.format_exc()
sys.exit(1)
del conn
return columns
def client_handler(connection, fingerprint):
try:
code = random.randrange(0, 999999999999999)
# print(code) # DEBUG
connection.send(b"\033[1mItrago WebTool Emergency Access Shell\033[0m\nPlease decrypt the following message to verify that you are the Administrator ([email protected])!\n")
connection.send(bytes("Encrypted Message:\n%s\n" % str(gpg.encrypt(str(code), fingerprint)),'utf-8'))
connection.send(b"Message: ")
if code == int(str(connection.recv(8192).decode('utf-8')).strip('\r').strip('\n')):
connection.send(b"\n\033[1mEmergency Shell Access Granted\033[0m\n")
while True:
try:
connection.send(b"$ ")
data = str(connection.recv(8192).decode('utf-8')).strip('\r').strip('\n').strip()
if data == "exit" or data == "quit":
connection.close()
break;
elif data == "copyright":
connection.send(b"\033[1mCopyright\033[0m\n")
connection.send(b"Tim Supelir Intrago Company XY Consultment\n")
elif data == "help":
connection.send(b"\033[1mHelppage\033[0m\n")
connection.send(b"<SQL Query> - Execute an SQL query\n")
connection.send(b"exit - Quit the emergency shell\n")
connection.send(b"quit - Quit the emergency shell\n")
connection.send(b"copyright - Show the copyright informations\n")
connection.send(b"help - Show this helppage\n")
else:
db = MySQL(*connection_data)
if data.upper().startswith("INSERT INTO"):
try:
rid = db.update(data)
connection.send(bytes("Okay, last inserted id: %s\n" % str(rid), 'utf-8'))
except Exception as e:
connection.send(
bytes(data + ": sql error\n",'utf-8'))
elif data.upper().startswith("SELECT") or data.upper().startswith("SHOW TABLES"):
try:
connection.send(bytes(str(db.queryAll(data)) + "\n", 'utf-8')) # convert to bytes
except Exception as e:
connection.send(bytes(data + ": sql error\n", 'utf-8'))
else:
connection.send(bytes(data + ": command not found\n", 'utf-8'))
except ValueError:
connection.send(b"\n")
connection.close()
else:
connection.send(b"Access Denied\n")
connection.close()
except ValueError:
connection.send(b"\n")
connection.close()
except BrokenPipeError:
connection.send(b"\n")
connection.close()
def toutiao_count():
# 账号信息
account_dir = "/mnt/data/toutiao_bak/account"
account_size = count(character, account_dir)
account_size = 0 if account_size == None else account_size
# 文章 微头条
article_micro_dir = "/mnt/data/toutiao_bak/article/micro"
article_text_dir = "/mnt/data/toutiao_bak/article/text"
article_video_dir = "/mnt/data/toutiao_bak/article/video"
article_micro_size = count(character, article_micro_dir)
article_text_size = count(character, article_text_dir)
article_video_size = count(character, article_video_dir)
article_micro_size = 0 if article_micro_size == None else article_micro_size
article_text_size = 0 if article_text_size == None else article_text_size
article_video_size = 0 if article_video_size == None else article_video_size
article_size = article_text_size + article_micro_size + article_video_size
# comment
comment_dir = "/mnt/data/toutiao_bak/comment"
comment_size = count(character, comment_dir)
comment_size = 0 if comment_size == None else comment_size
# update
update_micro_dir = "/mnt/data/toutiao_bak/update/micro"
update_text_dir = "/mnt/data/toutiao_bak/update/text"
update_video_dir = "/mnt/data/toutiao_bak/update/video"
update_micro_size = count(character, update_micro_dir)
update_text_size = count(character, update_text_dir)
update_video_size = count(character, update_video_dir)
update_micro_size = 0 if update_micro_size == None else update_micro_size
update_text_size = 0 if update_text_size == None else update_text_size
update_video_size = 0 if update_video_size == None else update_video_size
update_size = update_text_size + update_micro_size + update_video_size
# video
# video_dir = "/mnt/data/toutiao/video"
# video_size = video_count("mp4", video_dir)
# video_size = 0 if video_dir == None else video_size
total_data = account_size + article_size + update_size + comment_size
count_result = Field.get_fields()
count_result['total_data'] = total_data
count_result['account_total'] = account_size
count_result['article_total'] = article_size
count_result['update_total'] = update_size
count_result['comment_total'] = comment_size
count_result['article_micro_total'] = article_micro_size
count_result['article_text_total'] = article_text_size
count_result['article_video_total'] = article_video_size
count_result['updata_micro_total'] = update_micro_size
count_result['update_text_total'] = update_text_size
count_result['update_video_total'] = update_video_size
count_result['cycle_type'] = interval
mysql = MySQL(user=user, pwd=password, host=host, db=db, tb=toutiao_table)
mysql.update_data(count_result)
def arrange(dbconfig):
db = MySQL(dbconfig)
sql = "select * from cinema_movie_info_tab"
db.query(sql)
list = []
results = db.fetchAllRows()
for row in results:
print row
list.append(row)
return list
def get_comment(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.TABLE_COMMENT, (table_id, ))
table_comment = str(result[0].get("Data_Tbl_Cn_Nm")
or '').strip('\n').strip('\r').replace(';', '')
result = db.execute(const.FIELD_COMMENT, (table_id, ))
field_comment = [
str(rs.get("Fld_Cn_Nm")
or '').strip('\n').strip('\r').replace(';', '') for rs in result
]
del db
return table_comment, field_comment
def getDBs(tables):
try:
conn = MySQL(config.washmeta)
dbs = conn.execute(const.getDBs)
except Exception:
logging.error('获取库ID 失败！')
print traceback.format_exc()
sys.exit(1)
for table in tables:
for db in dbs:
if table.get('Dbid') == db.get('Db_Phys_Nm'):
table['Dbid'] = db['Dbid']
return tables
class Context():
@classmethod
async def get_context(cls):
if ContextHolder.context is None:
context = Context()
await context.startup()
ContextHolder.set_context(context)
return ContextHolder.context
def __init__(self):
is_dev = os.getenv('PYTHON_ENV') != 'production'
app = Application(debug=is_dev)
app.on_cleanup.append(self.cleanup)
old_config = load_config()
app['config'] = old_config
self._config = Config(is_dev=is_dev)
self._mysql = MySQL(self._config.mysql_config)
self._locales = Locales(self._config.locales_config)
self.is_dev = is_dev
self.app = app
async def startup(self):
await self._mysql.startup()
await self._locales.startup()
controllers.startup(self.app)
jinja2.startup(self.app, self._config, self._locales)
htmlmin.startup(self.app)
def cleanup(self, app):
self._mysql.cleanup()
@property
def is_prod(self):
return not self.is_dev
@property
def config(self):
return self._config
@property
def locales(self):
return self._locales
@property
def mysql(self):
return self._mysql
def html_parser(self, html_source):
tree = html.fromstring(html_source)
parser = HtmlParser()
username = parser.get_username(tree)
brief_info = parser.get_brief_info(tree)
industry = parser.get_industry(tree)
education = parser.get_education(tree)
major = parser.get_major(tree)
answer_count = parser.get_answer_count(tree)
article_count = parser.get_article_count(tree)
ask_question_count = parser.get_ask_question_count(tree)
collection_count = parser.get_collection_count(tree)
follower_count = parser.get_follower_count(tree)
followed_count = parser.get_followed_count(tree)
follow_live_count = parser.get_follow_live_count(tree)
follow_topic_count = parser.get_follow_topic_count(tree)
follow_column_count = parser.get_follow_column_count(tree)
follow_question_count = parser.get_follow_question_count(tree)
follow_collection_count = parser.get_follow_collection_count(tree)
now = datetime.datetime.now()
current_time = now.strftime("%Y-%m-%d %H:%M:%S")
print "*" * 60
print "用户名：%s\n" % username
print "个人简介：%s\n" % brief_info
print "所处行业：%s\n" % industry
print "毕业学校：%s\n" % education
print "主修专业：%s\n" % major
print "回答数：%s\n" % answer_count
print "文章数：%s\n" % article_count
print "提问数：%s\n" % ask_question_count
print "收藏数：%s\n" % collection_count
print "被关注数：%s\n" % follower_count
print "关注数：%s\n" % followed_count
print "关注直播数：%s\n" % follow_live_count
print "关注话题数：%s\n" % follow_topic_count
print "关注专栏数：%s\n" % follow_column_count
print "关注问题数：%s\n" % follow_question_count
print "关注收藏夹数：%s\n" % follow_collection_count
print "当前时间：%s\n" % current_time
print "*" * 60
# Save data to mysql.
db = MySQL(DATABASE_CONFIG)
sql = "INSERT INTO t_user(username, brief_info, industry, education, major, answer_count, article_count, ask_question_count, collection_count, follower_count, followed_count, follow_live_count, follow_topic_count, follow_column_count, follow_question_count, follow_collection_count, gmt_create) values('" + username + "','" + brief_info + "','" + industry + "','" + education + "', '" + major + "', '" + answer_count + "', '" + article_count + "', '" + ask_question_count + "', '" + collection_count + "', '" + follower_count + "', '" + followed_count + "', '" + follow_live_count + "', '" + follow_topic_count + "', '" + follow_column_count + "', '" + follow_question_count + "', '" + follow_collection_count + "', '" + current_time + "')"
db.insert(sql)
# Extract urls
self.extract_urls(tree)
return
def list(self,page, limit, cid,menu):
n = MySQL()
currRow = (int(page)-1)*int(limit)
n.selectDb('joke')
tbname = 'joke'
print "currRow:"+str(currRow)
conditions = (tbname,)
sqlCount = "SELECT FOUND_ROWS() c"
sql = "select j.id, j.title,j.view_count,j.created_at,c.title ctitle from %s j left join category c on j.category_id=c.category_id where 1=1 and j.content!='' "
if(cid):
print "cid:"+cid
sql += " AND j.category_id = %s"
conditions = conditions + (cid ,)
print "conditions:"+str(conditions)
conditions = conditions+(currRow,limit)
if(menu=="latest"):
sql += " order by j.created_at desc,j.id desc "
elif(menu=="hot"):
sql += " order by j.view_count desc,j.id desc "
sql += " limit %s,%s"
print "conditions:"+str(conditions)
sql = sql % conditions
print sql
n.query(sql)
ret = n.fetchAll()
n.query(sqlCount)
# totalCount = n.fetchAll()
totalCount = self.findCount(cid)
print "totalCount:"
print totalCount
print len(ret)
return ret,totalCount
def getTableID(tables1):
try:
conn = MySQL(config.washmeta)
tables2 = conn.execute(
'select Data_Tblid,Data_Tbl_Phys_Nm from data_tbl')
except Exception:
logging.error('获取表ID 失败！')
print traceback.format_exc()
sys.exit(1)
for t1 in tables1:
for t2 in tables2:
if t1.get('Data_Tblid') == t2.get('Data_Tbl_Phys_Nm'):
t1['Data_Tblid'] = t2.get('Data_Tblid')
return tables1
def __init__(self):
self._logger = Logger(__file__)
try:
fsock = open("sqls.xml", "r")
except IOError:
self._logger.error("The file don't exist, Please double check!")
self.sqls = BeautifulSoup(fsock.read())
dbconfig = {'host':'127.0.0.1',
'port': 3306,
'user':'******',
'passwd':'123456',
'db':'scenic',
'charset':'utf8'}
self.db = MySQL(dbconfig)
def insert(self,d):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.insert(tbname, d)
n.commit()
def __init__(self):
is_dev = os.getenv('PYTHON_ENV') != 'production'
app = Application(debug=is_dev)
app.on_cleanup.append(self.cleanup)
old_config = load_config()
app['config'] = old_config
self._config = Config(is_dev=is_dev)
self._mysql = MySQL(self._config.mysql_config)
self._locales = Locales(self._config.locales_config)
self.is_dev = is_dev
self.app = app
def douyin_count_user_id(users):
"""
统计抖音
:param users:
:return:
"""
out_account_file = "./data/douyin_account.txt"
indir = "/mnt/data/douyin_bak/account"
# 执行shell
system_count("ID:", indir=indir, outfile=out_account_file)
article_dir = "/mnt/data/douyin_bak/article" # 101989005631
out_article_file = "./data/douyin_article.txt"
character = "AU:"
system_count(character, article_dir, out_article_file)
time.sleep(60)
total_account_res = __count_all_users(out_account_file)
total_article_res = __count_all_users(out_article_file)
count = 1
for user in users:
user_id = user['user_id'].strip()
if user_id != None and user_id != "":
if user_id in total_account_res:
account_size = total_account_res[user_id]
else:
account_size = 0
if user_id in total_article_res:
article_size = total_article_res[user_id]
else:
article_size = 0
total = account_size + article_size
count_result = {
"total": total,
"article_total": article_size,
"account_total": account_size
}
if count % 10 == 0:
logger.info("开始统计第{}/{}个抖音账号{}".format(count, len(users),
user_id))
logger.info("抖音账号{}统计结果:{}".format(user_id, count_result))
mysql = MySQL(user=sql_user,
pwd=sql_password,
host=sql_host,
db=sql_db,
tb=douyin_detail_tb)
mysql.update_data_detail(count_result, user_id)
count += 1
logger.info("所有抖音账号统计完毕！\n\n")
async def save_file_to_database_server(path):
conn = MySQL()
db_config = {'host': 'localhost', 'user': '******', 'password': '******'}
try:
await conn.create_connection(db_config)
fileData = convertToBinaryData(path)
except FileNotFoundError:
print("File path not found...")
except Exception as err:
print(err)
else:
await conn.create_cursor()
sql = """INSERT INTO testData (fileName, fileData, fileSaveDate) VALUES ("{}", ?, "{}")""".format(
path, datetime.now())
await conn.execute_blob(sql, fileData)
await conn.commit()
await conn.close_cursor()
await conn.close_connection()
def get_metadata_field(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.TABLE_FIELD, (table_id, ))
del db
field = []
datatype = []
pk = []
null = []
for rs in result:
field.append(rs["fld_phys_nm"])
datatype.append(rs["fld_data_type"])
if rs["if_pk"] == 1:
pk.append(rs["fld_phys_nm"])
if rs["if_can_null"] == 0:
null.append(rs["fld_phys_nm"])
return field, datatype, pk, null
def __init__(self, config, sgdb=None):
Main._check_configuration(config)
self.cli = CLI()
self.config = config
self.log = LOG(self.config.get("log_dir", None))
self.sgdb = sgdb
if self.sgdb is None and not self.config.get("new_migration", None):
if self.config.get("database_engine") == 'mysql':
from mysql import MySQL
self.sgdb = MySQL(config)
elif self.config.get("database_engine") == 'oracle':
from oracle import Oracle
self.sgdb = Oracle(config)
elif self.config.get("database_engine") == 'mssql':
from mssql import MSSQL
self.sgdb = MSSQL(config)
elif self.config.get("database_engine") == 'postgresql':
from postgresql import PostgreSQL
self.sgdb = PostgreSQL(config)
else:
raise Exception("engine not supported '%s'" % self.config.get("database_engine"))
self.db_migrate = SimpleDBMigrate(self.config)
def init_db(self):
config = ConfigParser.ConfigParser()
config.read('/media/crawler/config/config.cfg')
db_config = config._sections['db']
db_config.pop('__name__')
from mysql import MySQL
self._db = MySQL(**db_config)
return self._db
def login():
data = request.json
if not data:
return jsonify({'error': 'JSON expected'}), 400
if 'username' not in data:
return jsonify({'error': 'missing key \'username\''}), 400
if 'password' not in data:
return jsonify({'error': 'missing key \'password\''}), 400
try:
db = MySQL(*db_data)
except Exception as e:
raise Exception('Database Error')
usr = db.query('SELECT password FROM itrago WHERE username = "******"'.format(data["username"]))
if usr is not None and usr.get("password") == hashlib.sha1(data['password'].encode()).hexdigest():
return jsonify({'msg': msg}), 200
else:
return jsonify({'error': 'wrong credentials'}), 400
def insert(self,d):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.insert(tbname, d)
n.commit()
def __init__(self, config=None, mysql=None, db_migrate=None):
self.cli = CLI()
self.config = config or {}
self.mysql = mysql
if self.mysql is None and not self.config.get("new_migration"):
self.mysql = MySQL(config)
self.db_migrate = db_migrate or SimpleDBMigrate(config)
def addViewCount(self,id):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
sql = "update {0} set view_count = view_count+1".format(tbname)
n.query(sql)
# n.update(tbname, { 'view_count': "view_count+1"}, "id='"+id+"'")
n.commit()
def __init__(self, config=None, mysql=None, db_migrate=None):
self.__cli = CLI()
self.__config = config
self.__mysql = mysql
if self.__mysql is None and not self.__config.get("new_migration"):
self.__mysql = MySQL(config)
self.__db_migrate = db_migrate
if self.__db_migrate is None:
self.__db_migrate = Migrations(config)
def findByName(self,name):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.query("select name,features from similar_images where name='"+name+"'")
return n.fetchRow()
def prev_next(self,type,joke,order):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
# 按日期
if type=="prev" and order=="date":
sql = "select * from {0} where content!='' and id> {1} and created_at>='{2}'".format(tbname,joke['id'],joke['created_at'])
orders = " order by created_at asc,id asc limit 1"
if type=="next" and order=="date":
sql = "select * from {0} where content!='' and id< {1} and created_at<='{2}'".format(tbname,joke['id'],joke['created_at'])
orders = " order by created_at desc,id desc limit 1"
#按分类
if type=="prev" and order=="cate":
sql = "select * from {0} where content!='' and id> {1} and created_at>='{2}' and category_id={3}".format(tbname,joke['id'],joke['created_at'],joke['category_id'])
orders = " order by created_at asc,id asc limit 1"
if type=="next" and order=="cate":
sql = "select * from {0} where content!='' and id< {1} and created_at<='{2}' and category_id={3}".format(tbname,joke['id'],joke['created_at'],joke['category_id'])
orders = " order by created_at desc,id desc limit 1"
print "".join([sql,orders])
n.query("".join([sql,orders]))
joke = n.fetchRow()
return joke
def findByTitle(self,v):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select name from %s where title = %s" %(tbname,v))
return n.fetchAll()
def findByTitleDate(self,title,created_at):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select id from %s where title = '%s' and created_at= '%s'" %(tbname,title,created_at))
return n.fetchAll()
def findById(self,v):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select j.id, j.title,j.content,j.view_count,j.created_at,j.category_id,c.title ctitle from %s j left join category c on j.category_id=c.category_id where j.id = %s" %(tbname,v))
return n.fetchAll()
def findAll(self):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.query("select name,features from similar_images")
return n.fetchAll()
def findAll(self):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
# n.query("select name from %s where updated_at <'2016-06-27 00:00:00'" %(tbname) )
# n.query("select name from %s where is_face!=1 " %(tbname) )
n.query("select * from %s " %(tbname) )
return n.fetchAll()
def findCount(sefl,cid):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
conditions = (tbname,)
sqlCount = "SELECT count(id) c from %s where content!=''"
if(cid):
sqlCount += " AND category_id = %s"
conditions = conditions+(cid ,)
sqlCount = sqlCount % conditions
n.query(sqlCount)
totalCount = n.fetchAll()
return totalCount[0]['c']
class Common:
_db = None
def __init__(self):
pass
def init_db(self):
config = ConfigParser.ConfigParser()
config.read('/media/crawler/config/config.cfg')
db_config = config._sections['db']
db_config.pop('__name__')
from mysql import MySQL
self._db = MySQL(**db_config)
return self._db
def get_urls(self, status=0):
sql = "SELECT url FROM url WHERE status='%s'" % status
urls = self._db.get_all(sql)
ret = []
if urls:
for i in urls:
ret.append(i['url'])
return ret
import Queue
import threading
import time
import sys,os
import traceback
import logging
import pdb
#homeDir = os.getcwd()
dbconfig = {'host':dbconf.g_resultDBHost,
'port':dbconf.g_resultDBPort,
'user':dbconf.g_resultDBUser,
'passwd':dbconf.g_resultDBPwd,
'db':dbconf.g_resultDBName,
'charset':'utf8'}
db = MySQL(dbconfig)
sql = "select cID from available_CIDs;"
cLists = db.fetchAllRows(sql)
sql2 = "select * from available_CIDs;"
cidsNum = db.query(sql2)
class WorkManager(object):
def __init__(self,work_num=10,thread_num=2):
self.work_queue = Queue.Queue()
self.threads = []
self.__init_work_queue(work_num)
self.__init_thread_pool(thread_num)
### Initialize thread
def __init_thread_pool(self,thread_num):
for i in range(thread_num):
def updateJoke(self,content,id):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.update(tbname, { 'content': content}, "id='"+id+"'")
n.commit()
import glob
import sys,os
import datetime
import re
dataDir = "./data/"
sqlDir = '../sql/'
today = datetime.date.today()
lastDay = today - datetime.timedelta(days = 1)
dbconfig = {'host':dbconf.g_resultDBHost,
'port':dbconf.g_resultDBPort,
'user':dbconf.g_resultDBUser,
'passwd':dbconf.g_resultDBPwd,
'db':dbconf.g_resultDBName,
'charset':'utf8'}
db = MySQL(dbconfig)
sql1 = "select cID from available_CIDs;"
cLists = db.fetchAllRows(sql1)
files = glob.glob(dataDir+'record_*')
lastDay="2015-12-22"
def getSqlName(cid):
f = open(dataDir+"record_"+str(cid))
return f.readline().split()[0]
def getFileSize(cid,dateStr):
print "%s%s-%s.sql"%(sqlDir,cid,dateStr)
return os.path.getsize("%s%s-%s.sql"%(sqlDir,cid,dateStr))
def getSeekValue(cid):
import glob
import re
logDir = "./log/"
dataDir = "./data/"
cids=[34013601,41013401]
r=r"IMPORT RESULT: (.*)"
rc=re.compile(r)
dbconfig = {'host':dbconf.g_resultDBHost,
'port':dbconf.g_resultDBPort,
'user':dbconf.g_resultDBUser,
'passwd':dbconf.g_resultDBPwd,
'db':dbconf.g_resultDBName,
'charset':'utf8'}
db = MySQL(dbconfig)
def getExecNum(str):
sql = "select * from import_status_record where curr_sql_file='%s';"%(str)
return db.query(sql)
def calTime(start,end):
start=time.strptime(start,"%Y-%m-%d_%H:%M:%S")
end=time.strptime(end,"%Y-%m-%d_%H:%M:%S")
start=datetime.datetime(start[0],start[1],start[2],start[3],start[4],start[5])
end=datetime.datetime(end[0],end[1],end[2],end[3],end[4],end[5])
return end-start
def getFile(str):
class Main(object):
def __init__(self, config=None, mysql=None, db_migrate=None):
self.cli = CLI()
self.config = config or {}
self.mysql = mysql
if self.mysql is None and not self.config.get("new_migration"):
self.mysql = MySQL(config)
self.db_migrate = db_migrate or SimpleDBMigrate(config)
def execute(self):
self.cli.msg("\nStarting DB migration...", "PINK")
if self.config.get("new_migration"):
self.create_migration()
else:
self.migrate()
self.cli.msg("\nDone.\n", "PINK")
def create_migration(self):
# TODO: create file in the migrations directory, not in current
new_file = Migration.create(self.config.get("new_migration"))
self.cli.msg("- Created file '%s'" % (new_file))
def migrate(self):
destination_version = self.get_destination_version()
current_version = self.mysql.get_current_schema_version()
self.cli.msg("- Current version is: %s" % current_version, "GREEN")
self.cli.msg("- Destination version is: %s" % destination_version, "GREEN")
# if current and destination versions are the same,
# will consider a migration up to execute remaining files
is_migration_up = True
if int(current_version) > int(destination_version):
is_migration_up = False
# do it!
self.execute_migrations(current_version, destination_version, is_migration_up)
def get_destination_version(self):
destination_version = self.config.get("schema_version")
if destination_version is None:
destination_version = self.db_migrate.latest_version_available()
if destination_version is not '0' and not self.db_migrate.check_if_version_exists(destination_version):
raise Exception("version not found (%s)" % destination_version)
return destination_version
def get_migration_files_to_be_executed(self, current_version, destination_version):
mysql_versions = self.mysql.get_all_schema_versions()
migration_versions = self.db_migrate.get_all_migration_versions()
# migration up: the easy part
if current_version <= destination_version:
remaining_versions_to_execute = Lists.subtract(migration_versions, mysql_versions)
remaining_versions_to_execute = [version for version in remaining_versions_to_execute if version <= destination_version]
return remaining_versions_to_execute
# migration down...
down_versions = [version for version in mysql_versions if version <= current_version and version > destination_version]
for version in down_versions:
if version not in migration_versions:
raise Exception("impossible to migrate down: one of the versions was not found (%s)" % version)
down_versions.reverse()
return down_versions
def execute_migrations(self, current_version, destination_version, is_migration_up):
# getting only the migration sql files to be executed
versions_to_be_executed = self.get_migration_files_to_be_executed(current_version, destination_version)
if versions_to_be_executed is None or len(versions_to_be_executed) == 0:
self.cli.msg("\nNothing to do.\n", "PINK")
return
up_down_label = is_migration_up and "up" or "down"
if self.config.get("show_sql_only"):
self.cli.msg("\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW")
else:
self.cli.msg("\nStarting migration %s!" % up_down_label)
if self.config.get("log_level") >= 1:
self.cli.msg("*** versions: %s\n" % versions_to_be_executed, "CYAN")
sql_statements_executed = []
for migration_version in versions_to_be_executed:
migration = self.db_migrate.get_migration_from_version_number(migration_version)
sql = is_migration_up and migration.sql_up or migration.sql_down
if not self.config.get("show_sql_only"):
if self.config.get("log_level") >= 1:
self.cli.msg("===== executing %s (%s) =====" % (migration.file_name, up_down_label))
log = None
if self.config.get("log_level") >= 2:
log = self.cli.msg
self.mysql.change(sql, migration_version, is_migration_up, execution_log=log)
# paused mode
if self.config.get("paused_mode"):
raw_input("* press <enter> to continue... ")
# recording the last statement executed
sql_statements_executed.append(sql)
if self.config.get("show_sql") or self.config.get("show_sql_only"):
self.cli.msg("__________ SQL statements executed __________", "YELLOW")
for sql in sql_statements_executed:
self.cli.msg(sql, "YELLOW")
self.cli.msg("_____________________________________________", "YELLOW")
from threadPool import WorkManager
import time,sys,os
from mysql import MySQL
import dbconf
logDir = "./log/"
sqlData="/tmp/data"
execNum = 0
dbconfig = {'host':dbconf.g_resultDBHost,
'port':dbconf.g_resultDBPort,
'user':dbconf.g_resultDBUser,
'passwd':dbconf.g_resultDBPwd,
'db':dbconf.g_resultDBName,
'charset':'utf8'}
db = MySQL(dbconfig)
#sql1 = "select tried_num from import_status_record where cid = '%s' and curr_sql_file like '%s';"%(cLists[0],'%'+sqlDate+'%')
#execNum=db.query(sql1)
#sql = "select count(*) from available_CIDs;"
sql2 = "select * from available_CIDs;"
cidsNum = db.query(sql2)
if __name__ == "__main__":
try:
#saveout = sys.stdout
#saveerr = sys.stderr
#f = open("log/import_record"+time.strftime("%m%d_%H%M%S"),"w")
#sys.stdout = f
#sys.stderr = f
#os.system('./stat.sh ' + "config")
class Main(object):
def __init__(self, config=None, mysql=None, db_migrate=None):
self.__cli = CLI()
self.__config = config
self.__mysql = mysql
if self.__mysql is None and not self.__config.get("new_migration"):
self.__mysql = MySQL(config)
self.__db_migrate = db_migrate
if self.__db_migrate is None:
self.__db_migrate = Migrations(config)
def execute(self):
self.__cli.msg("\nStarting DB migration...", "PINK")
if self.__config.get("new_migration"):
self._create_migration()
else:
self._migrate()
self.__cli.msg("\nDone.\n", "PINK")
def _create_migration(self):
new_file = self.__db_migrate.create_migration(self.__config.get("new_migration"))
self.__cli.msg("- Created file '%s'" % (new_file))
def _migrate(self):
destination_version = self._get_destination_version()
current_version = self.__mysql.get_current_schema_version()
self.__cli.msg("- Current version is: %s" % current_version, "GREEN")
self.__cli.msg("- Destination version is: %s" % destination_version, "GREEN")
# if current and destination versions are the same,
# will consider a migration up to execute remaining files
is_migration_up = True
if int(current_version) > int(destination_version):
is_migration_up = False
# do it!
self._execute_migrations(current_version, destination_version, is_migration_up)
def _get_destination_version(self):
destination_version = self.__config.get("schema_version")
if destination_version is None:
destination_version = self.__db_migrate.latest_schema_version_available()
if not self.__db_migrate.check_if_version_exists(destination_version):
self.__cli.error_and_exit("version not found (%s)" % destination_version)
return destination_version
def _get_migration_files_to_be_executed(self, current_version, destination_version):
mysql_versions = self.__mysql.get_all_schema_versions()
migration_versions = self.__db_migrate.get_all_migration_versions()
# migration up: the easy part
if current_version <= destination_version:
remaining_versions_to_execute = Lists.subtract(migration_versions, mysql_versions)
remaining_versions_to_execute = [
version for version in remaining_versions_to_execute if version <= destination_version
]
return remaining_versions_to_execute
# migration down...
down_versions = [
version for version in mysql_versions if version <= current_version and version > destination_version
]
for version in down_versions:
if version not in migration_versions:
self.__cli.error_and_exit(
"impossible to migrate down: one of the versions was not found (%s)" % version
)
down_versions.reverse()
return down_versions
def _execute_migrations(self, current_version, destination_version, is_migration_up):
# getting only the migration sql files to be executed
versions_to_be_executed = self._get_migration_files_to_be_executed(current_version, destination_version)
if versions_to_be_executed is None or len(versions_to_be_executed) == 0:
self.__cli.msg("\nNothing to do.\n", "PINK")
sys.exit(0)
up_down_label = "up" if is_migration_up else "down"
if self.__config.get("show_sql_only"):
self.__cli.msg("WARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW")
else:
self.__cli.msg("\nStarting migration %s!" % up_down_label)
self.__cli.msg("*** versions: %s\n" % versions_to_be_executed, "GRAY")
sql_statements_executed = []
for migration_version in versions_to_be_executed:
sql_file = self.__db_migrate.get_migration_file_name_from_version_number(migration_version)
sql = self.__db_migrate.get_sql_command(sql_file, is_migration_up)
if not self.__config.get("show_sql_only"):
self.__cli.msg("===== executing %s (%s) =====" % (sql_file, up_down_label))
self.__mysql.change(sql, migration_version, is_migration_up)
# recording the last statement executed
sql_statements_executed.append(sql)
if self.__config.get("show_sql") or self.__config.get("show_sql_only"):
self.__cli.msg("__________ SQL statements executed __________", "YELLOW")
for sql in sql_statements_executed:
self.__cli.msg(sql, "YELLOW")
self.__cli.msg("_____________________________________________", "YELLOW")
import sys,os
import traceback
import re
import glob
import copy
logDir = "./log/"
dataDir = "./data/"
sqlDir = "../binlog/"
dbconfig = {'host':dbconf.g_resultDBHost,
'port':dbconf.g_resultDBPort,
'user':dbconf.g_resultDBUser,
'passwd':dbconf.g_resultDBPwd,
'db':dbconf.g_resultDBName,
'charset':'utf8'}
db = MySQL(dbconfig)
sql1 = "select cID from available_CIDs;"
cids = db.fetchAllRows(sql1)
sql2 = "select * from available_CIDs;"
cidsNum = db.query(sql2)
class WorkManager(object):
def __init__(self,work_num=10,thread_num=2):
self.work_queue = Queue.Queue()
self.threads = []
self.__init_work_queue(work_num)
self.__init_thread_pool(thread_num)
### Initialize thread
def __init_thread_pool(self,thread_num):
for i in range(thread_num):
from mysql import MySQL
dbconfig = {"host": "172.16.2.226", "port": 3306, "user": "******", "passwd": "root", "db": "sun_test", "charset": "utf8"}
db = MySQL(dbconfig)
# sql = "select * from cinema_movie_info_tab"
# sql = "/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;"
# sql = "/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;"
sql = "/*!40019 SET @@session.max_insert_delayed_threads=0*/;"
try:
db.update(sql)
except Exception, e:
print "err:" + sql
raise e
# db.insert(sql)
result = db.fetchAllRows()
print result
# for row in result:
# print "**"*30
# print row[0]
# for colum in row:
# print colum
db.close()
class Main(object):
def __init__(self, config=None, mysql=None, db_migrate=None):
self.cli = CLI()
self.config = config or {}
self.log = LOG(self.config.get("log_dir"))
self.mysql = mysql
if self.mysql is None and not self.config.get("new_migration"):
self.mysql = MySQL(config)
self.db_migrate = db_migrate or SimpleDBMigrate(config)
def execution_log(self, msg, color="CYAN", log_level_limit=2):
if self.config.get("log_level") >= log_level_limit:
self.cli.msg(msg)
self.log.debug(msg)
def execute(self):
self.execution_log("\nStarting DB migration...", "PINK", log_level_limit=1)
if self.config.get("new_migration"):
self.create_migration()
else:
self.migrate()
self.execution_log("\nDone.\n", "PINK", log_level_limit=1)
def create_migration(self):
migrations_dir = self.config.get("migrations_dir")
new_file = Migration.create(self.config.get("new_migration"), migrations_dir[0], self.config.get("db_script_encoding", "utf-8"))
self.execution_log("- Created file '%s'" % (new_file), log_level_limit=1)
def migrate(self):
destination_version = self.get_destination_version()
current_version = self.mysql.get_current_schema_version()
# do it!
self.execute_migrations(current_version, destination_version)
def get_destination_version(self):
destination_version = self.config.get("schema_version")
if destination_version is None:
destination_version = self.db_migrate.latest_version_available()
if destination_version is not '0' and not self.db_migrate.check_if_version_exists(destination_version):
raise Exception("version not found (%s)" % destination_version)
return destination_version
def get_migration_files_to_be_executed(self, current_version, destination_version, is_migration_up):
mysql_versions = self.mysql.get_all_schema_versions()
migration_versions = self.db_migrate.get_all_migration_versions()
# migration up
if is_migration_up:
remaining_versions_to_execute = Lists.subtract(migration_versions, mysql_versions)
remaining_migrations_to_execute = [self.db_migrate.get_migration_from_version_number(version) for version in remaining_versions_to_execute if version <= destination_version]
return remaining_migrations_to_execute
# migration down...
destination_version_id = self.mysql.get_version_id_from_version_number(destination_version)
migrations = self.mysql.get_all_schema_migrations()
down_migrations_to_execute = [migration for migration in migrations if migration.id > destination_version_id]
for migration in down_migrations_to_execute:
if not migration.sql_down:
if migration.version not in migration_versions:
raise Exception("impossible to migrate down: one of the versions was not found (%s)" % migration.version)
migration_tmp = self.db_migrate.get_migration_from_version_number(migration.version)
migration.sql_up = migration_tmp.sql_up
migration.sql_down = migration_tmp.sql_down
migration.file_name = migration_tmp.file_name
down_migrations_to_execute.reverse()
return down_migrations_to_execute
def execute_migrations(self, current_version, destination_version):
"""
passed a version:
this version don't exists in the database and is younger than the last version -> do migrations up until the this version
this version don't exists in the database and is older than the last version -> do nothing, is a unpredictable behavior
this version exists in the database and is older than the last version -> do migrations down until this version
didn't pass a version -> do migrations up until the last available version
"""
is_migration_up = True
# check if a version was passed to the program
if self.config.get("schema_version"):
# if was passed and this version is present in the database, check if is older than the current version
destination_version_id = self.mysql.get_version_id_from_version_number(destination_version)
if destination_version_id:
current_version_id = self.mysql.get_version_id_from_version_number(current_version)
# if this version is previous to the current version in database, then will be done a migration down to this version
if current_version_id > destination_version_id:
is_migration_up = False
# if was passed and this version is not present in the database and is older than the current version, raise an exception
# cause is trying to go down to something that never was done
elif current_version > destination_version:
raise Exception("Trying to migrate to a lower version wich is not found on database (%s)" % destination_version)
# getting only the migration sql files to be executed
migrations_to_be_executed = self.get_migration_files_to_be_executed(current_version, destination_version, is_migration_up)
self.execution_log("- Current version is: %s" % current_version, "GREEN", log_level_limit=1)
if migrations_to_be_executed is None or len(migrations_to_be_executed) == 0:
self.execution_log("- Destination version is: %s" % current_version, "GREEN", log_level_limit=1)
self.execution_log("\nNothing to do.\n", "PINK", log_level_limit=1)
return
self.execution_log("- Destination version is: %s" % (is_migration_up and migrations_to_be_executed[-1].version or destination_version), "GREEN", log_level_limit=1)
up_down_label = is_migration_up and "up" or "down"
if self.config.get("show_sql_only"):
self.execution_log("\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW", log_level_limit=1)
else:
self.execution_log("\nStarting migration %s!" % up_down_label, log_level_limit=1)
if self.config.get("log_level") >= 1:
self.execution_log("*** versions: %s\n" % ([ migration.version for migration in migrations_to_be_executed]), "CYAN", log_level_limit=1)
sql_statements_executed = []
for migration in migrations_to_be_executed:
sql = is_migration_up and migration.sql_up or migration.sql_down
if not self.config.get("show_sql_only"):
if self.config.get("log_level") >= 1:
self.execution_log("===== executing %s (%s) =====" % (migration.file_name, up_down_label), log_level_limit=1)
self.mysql.change(sql, migration.version, migration.file_name, migration.sql_up, migration.sql_down, is_migration_up, execution_log=self.execution_log)
# paused mode
if self.config.get("paused_mode"):
raw_input("* press <enter> to continue... ")
# recording the last statement executed
sql_statements_executed.append(sql)
if self.config.get("show_sql") or self.config.get("show_sql_only"):
self.execution_log("__________ SQL statements executed __________", "YELLOW", log_level_limit=1)
for sql in sql_statements_executed:
self.execution_log(sql, "YELLOW", log_level_limit=1)
self.execution_log("_____________________________________________", "YELLOW", log_level_limit=1)
from mysql import MySQL
from app.config import Config
# Create the database from the config
MySQL.create_database(Config.get('db').get('database'))
MySQL.run('''
CREATE TABLE IF NOT EXISTS user (
user_id INT(8) UNSIGNED,
creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id)
)
''')
MySQL.run('''
CREATE TABLE IF NOT EXISTS user_device (
device_id INT(8) UNSIGNED,
user_id INT(8) UNSIGNED,
platform VARCHAR(255) CHARACTER SET utf8,
system_version VARCHAR(255) CHARACTER SET utf8,
app_version VARCHAR(255) CHARACTER SET utf8,
push_token VARCHAR(255) CHARACTER SET utf8,
creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_seen TIMESTAMP,
PRIMARY KEY (device_id)
)
''')
MySQL.run('''
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
sys.path.insert(0, 'lib')
from mysql import MySQL
import ConfigParser
import logging
config = ConfigParser.ConfigParser()
config.read('config/config.cfg')
db_config = config._sections['db']
db_config.pop('__name__')
a = MySQL(**db_config)
# sql = "show tables"
sql = "insert into url (post_id, url, status) values ('11', 'http://www.baidu.com', '0')"
a.query(sql)
a.commit()
# aa = a.get_all(sql)
# print aa