def query_db(query, args=(), one=False):
with contextlib.closing(sqlite.connect(getDbFile())) as con:
cur = con.cursor()
cur.execute(query, args)
r = [dict((cur.description[i][0], value) \
for i, value in enumerate(row)) for row in cur.fetchall()]
return (r[0] if r else None) if one else r
def InitDb():
if not os.path.exists(GetDbDir()):
os.mkdir(GetDbDir())
if not os.path.exists(GetDbDir() + "version"):
with contextlib.closing(sqlite.connect(getDbFile())) as con:
sqlfile = os.path.join(GetDbDir(), "db/subnodedb", versionIter[0])
logger.info("刷%s 脚本", sqlfile)
with open(sqlfile, encoding='utf-8') as f:
sqltxt = f.read()
cur = con.cursor()
cur.executescript(sqltxt)
with open(os.path.join(GetDbDir(), "version"), "w+") as f:
f.write("0")
with open(GetDbDir() + "version") as f:
version = int(f.read())
if version < len(versionIter) - 1:
logger.warning("版本更新, %d -> %d", version, len(versionIter) - 1)
for i in range(version, len(versionIter)):
dbfile = os.path.join(GetDbDir(), "superdb.db")
with contextlib.closing(sqlite.connect(dbfile)) as con:
sqlfile = os.path.join(GetDbDir(), "db/subnodedb",
versionIter[i])
logger.info("刷%s 脚本", sqlfile)
with open(sqlfile, encoding='utf-8') as f:
sqltxt = f.read()
cur = con.cursor()
cur.executescript(sqltxt)
def DbStateDel():
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
c.execute("delete from state")
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
def DbStateUpdate(account,
region,
role=None,
curlevel=None,
zhiye=None,
curpilao=None,
money=None,
wuse=None,
kicktime=None,
kicklong=None,
timeup=False):
with contextlib.closing(sqlite.connect(getDbFile())) as con:
DbStateInsert(account, region, role)
c = con.cursor()
c.execute("begin")
try:
if role is not None:
conditionstr = " where account = ? and region = ? and role = ?"
conditiontup = (account, region, role)
else:
conditionstr = " where account = ? and region = ?"
conditiontup = (account, region)
if curlevel is not None:
c.execute("update state set curlevel = ? %s" % conditionstr,
(curlevel, ) + conditiontup)
if zhiye is not None:
c.execute("update state set zhiye = ? %s" % conditionstr,
(zhiye, ) + conditiontup)
if curpilao is not None:
c.execute("update state set curpilao = ? %s" % conditionstr,
(curpilao, ) + conditiontup)
if money is not None:
c.execute("update state set money = ? %s" % conditionstr,
(money, ) + conditiontup)
if wuse is not None:
c.execute("update state set wuse = ? %s" % conditionstr,
(wuse, ) + conditiontup)
if kicktime is not None:
c.execute("update state set kicktime = ? %s" % conditionstr,
(kicktime, ) + conditiontup)
if kicklong is not None:
c.execute("update state set kicklong = ? %s" % conditionstr,
(kicklong, ) + conditiontup)
if timeup:
t = time.time()
c.execute("update state set timepoint = ? %s" % conditionstr,
(int(t), ) + conditiontup)
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
def DbEventAppend(account, region, role, content):
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
c.execute(
"insert into event (account, region, role, timepoint, content) values(?, ?, ?, ?, ?)",
(account, region, role, int(time.time()), content))
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
def CreateJueseAppend(account, region, juese):
yyyymmdd = datetime.datetime.today().strftime('%Y-%m-%d')
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
c.execute(
"insert into createrole (account, region, juese, yyyymmdd) values (?, ?, ?, ?)",
(account, region, juese, yyyymmdd))
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
def AccountXingyunxingRule(account, region):
count = 0
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
c.execute(
"select count(*) from state where account=? and region=? and curlevel >= 25",
(account, region))
rows = c.fetchall()
count = rows[0][0]
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
return count > 0
def DayCreateJueseNum(account, region):
yyyymmdd = datetime.datetime.today().strftime('%Y-%m-%d')
count = 0
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
c.execute(
"select count(*) from createrole where account=? and region=? and yyyymmdd=?",
(account, region, yyyymmdd))
rows = c.fetchall()
count = rows[0][0]
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
return count
def IsAccoutnZhicai(account):
count = 0
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
c.execute(
"select count(*) from state where kicktime IS NOT NULL and datetime(kicktime, 'unixepoch', 'localtime') "
"< datetime('now', 'localtime') and datetime('now', 'localtime') < datetime(kicktime + kicklong, 'unixepoch', "
"'localtime') and account=?", (account, ))
rows = c.fetchall()
count = rows[0][0]
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
return count > 0
def DbStateInsert(account, region, role):
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
c.execute(
"select count(*) from state where account=? and region=? and role=?",
(account, region, role))
rows = c.fetchall()
if rows[0][0] == 0:
c.execute(
"insert into state (account, region, role) values (?, ?, ?)",
(account, region, role))
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
def DbItemAppend(account,
region,
role,
moneyadd=None,
wuseadd=None,
timeadd=None):
with contextlib.closing(sqlite.connect(getDbFile())) as con:
DbItemInsert(account, region, role)
c = con.cursor()
c.execute("begin")
try:
yyyymmdd = datetime.datetime.today().strftime('%Y%m%d')
conditionstr = " where account = ? and region = ? and role = ? and yyyymmdd = ?"
conditiontup = (account, region, role, yyyymmdd)
c.execute(
"select todaymoney, todaywuse, todaysumtime from item %s" %
conditionstr, conditiontup)
rows = c.fetchall()
for row in rows:
todaymoney, todaywuse, todaysumtime = row[0], row[1], row[2]
if moneyadd is not None:
c.execute(
"update item set todaymoney = ? %s" % conditionstr,
(todaymoney + moneyadd, ) + conditiontup)
if wuseadd is not None:
c.execute(
"update item set todaywuse = ? %s" % conditionstr,
(todaywuse + wuseadd, ) + conditiontup)
if timeadd is not None:
c.execute(
"update item set todaysumtime = ? %s" % conditionstr,
(todaysumtime + timeadd, ) + conditiontup)
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")
def DbItemInsert(account, region, role):
with contextlib.closing(sqlite.connect(getDbFile())) as con:
c = con.cursor()
c.execute("begin")
try:
yyyymmdd = datetime.datetime.today().strftime('%Y%m%d')
c.execute(
"select count(*) from item where account=? and region=? and role=? and yyyymmdd=?",
(account, region, role, yyyymmdd))
rows = c.fetchall()
if rows[0][0] == 0:
c.execute(
"insert into item (account, region, role, yyyymmdd, todaymoney, todaywuse, todaysumtime) values (?, ?, ?, ?, ?, ?, ?)",
(account, region, role, yyyymmdd, 0, 0, 0))
c.execute("commit")
except con.Error as e:
logger.warning("sql error! %s" % e)
c.execute("rollback")