def db_connect(self):
if self.db_type == "mysql":
database_url = "mysql://{}:{}@{}:{}/{}".format(
self.username, self.password, self.host, self.port,
self.db_name)
db = records.Database(database_url,
connect_args={"charset": "utf8"})
if self.db_type == "oracle":
try:
database_url = "oracle+cx_oracle://{}:{}@{}:{}/?service_name={}".format(
self.username, self.password, self.host, self.port,
self.db_name)
db = records.Database(database_url)
except:
database_url = "oracle+cx_oracle://{}:{}@{}:{}/?sid={}".format(
self.username, self.password, self.host, self.port,
self.db_name)
db = records.Database(database_url)
if self.db_type == "mssql":
database_url = "mssql+pymssql://{}:{}@{}:{}/{}".format(
self.username, self.password, self.host, self.port,
self.db_name)
db = records.Database(database_url,
connect_args={"charset": "utf8"})
return db
def get_db(self):
if self.db is None:
if 'sqlite' == self.dbtype:
db = records.Database('sqlite:///%s' % self.dbname)
elif 'mysql' == self.dbtype:
db = records.Database('mysql+pymysql://%s:%s@%s/%s?charset=utf8' % (self.dbuser, self.dbpass, self.dbaddr, self.dbname))
self.db = db
return self.db
def initialize(self):
if not os.path.exists("shell.db"):
self.db = records.Database('sqlite:///shell.db', connect_args={'check_same_thread': False})
self.db.query('DROP TABLE IF EXISTS shell')
self.db.query(
'CREATE TABLE shell (id INTEGER PRIMARY KEY autoincrement,types text,shell text, passwd text, config text ,remark text, typeid text,coding text,createdtime text,updatetime,text)')
else:
self.db = records.Database('sqlite:///shell.db', connect_args={'check_same_thread': False})
def __init__(self, collection, configfile='./config/config.json') -> None:
with open(configfile) as config_file:
config = json.load(config_file)
if (collection in [*config["collections_info_backend"]]):
self.db = records.Database(
config["collections_info_backend"][collection])
else:
self.db = records.Database(
config["collections_info_backend"]['default'])
def __init__(self, dbname=None, server=None, username=None, pwd=None, port=None):
server = server or MYSQL_SERVER[ENV].value
username = username or MYSQL_USER
pwd = pwd or MYSQL_PASSWORD
port = port or DEF_MYSQL_PORT
if dbname:
self.db = records.Database(
'mysql+pymysql://{}:{}@{}:{}/{}'.format(username, pwd, server, port, dbname))
else:
self.db = records.Database(
'mysql+pymysql://{}:{}@{}:{}'.format(username, pwd, server, port))
def test_christmas(self, info):
url = self.hosts + "/api/activity/christmas/join"
for i in info:
headers = {'Authorization': i["token"]}
a = 10
a = a + 10
available_before = self.account.query(
"SELECT available from account_balance WHERE user_id =%s and currency_id = 64"
% i["uid"])
available_before = available_before.dataset._data[0][0]
print("该用户持仓数量：" + str(available_before))
account = records.Database(
'mysql+pymysql://root:[email protected]:3306/btb_account?charset=utf8'
)
reward_pool = account.query(
"SELECT available from account_balance WHERE account_no = 'ACN00527601385577701445323' and currency_id = 64"
)
reward_pool = reward_pool.dataset._data[0][0]
print("奖池余额：" + str(reward_pool))
result = requests.post(url, headers=headers)
result = json.loads(result.text)
if result["code"] == 0 and result["data"] != "":
account = records.Database(
'mysql+pymysql://root:[email protected]:3306/btb_account?charset=utf8'
)
available_after = account.query(
"SELECT available from account_balance WHERE user_id =%s and currency_id = 64"
% i["uid"])
available_after = available_after.dataset._data[0][0]
print("用户id：" + str(i["uid"]))
print("用户助力后余额：" + str(available_after))
if available_after - available_before != 3:
print("测试失败")
else:
print("测试成功")
elif result["code"] == 502:
if available_before < 10000:
print("测试通过，用户持仓不足10000")
elif reward_pool < 3:
print("测试成功，帐户余额不足")
elif reward_pool > 3:
print("测试失败")
elif result["code"] == 0 and result["data"] == "":
print("测试通过， 再次点击")
else:
print("请求失败")
def create_user(can, name, display_name, phone_number, active=True):
"""
Create a new user.
:param can: CAN (compulsory)
:param name: Username (not compulsory)
:param display_name: Friendly name for display on UI (compulsory)
:param phone_number: Phone number for push notifs (not compulsory)
:param active: Is active (defaults to True)
:return: Id of new user or False if couldn't create
"""
# prepare the params in one nice block here before passing it into the query
params = {
'can': transform_can_to_canonical(can),
'name': str(name),
'display_name': str(display_name),
'phone_number': str(phone_number),
'active': bool(active)
}
with records.Database(DATABASE_URL) as db:
try:
return db.query(
'''
INSERT INTO users (
can, name, display_name, phone_number, active
)
VALUES (:can,:name,:display_name,:phone_number,:active)
RETURNING id;
''', **params).first()['id']
except IntegrityError as e:
print('IntegrityError! {}'.format(repr(e)))
return False
def __init__(self, database_url):
self.logger = api_logger()
try:
self.db = records.Database(database_url)
except Exception, e:
self.logger.error(e.message)
raise e
def start_callback(payload, event):
sender_id = event.sender_id.decode('utf-8')
pg.typing_on(sender_id)
db = records.Database('mysql://<user>:<password>@<url>:3306/db')
rec = db.query("select * from user where fbid = %s" % sender_id)
if len(rec.as_dict()) == 0:
user = pg.get_user_profile(sender_id)[u'first_name']
pg.send(sender_id, "Hey %s, please send me your MAC ID" % user)
else:
pg.typing_on(sender_id)
quick_replies = [
{
'title': 'Charging status',
'payload': 'charge_stat'
},
{
'title': 'Last saved',
'payload': 'l_saved'
},
{
'title': 'Total saving',
'payload': 'c_saved'
},
]
pg.send(sender_id,
"What do you want to know?",
quick_replies=quick_replies)
def reloadMySql(dbname):
mysql_dict = db_config.config.get(dbname)
if mysql_dict:
database_url = "mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}".format(
**mysql_dict)
# 设置连接池的失效时间，解决"MySQL server has gone away"
DBMySql.db_mysql[dbname] = records.Database(database_url, pool_recycle=60*50)
def __init__(self):
"""Initiate username , password , name of the database and connect."""
self.db_name = DB_NAME
self.db_username = DB_USER
self.db_password = DB_PASS
self.db = records.Database('mysql+mysqlconnector://{}:{}@localhost/?charset=utf8mb4'
.format(self.db_username, self.db_password))
def psqlGetRecord(uuid, table_name):
'''Returns an individual record from database table given using "uuid" to
query for it.
'''
#Connect to database
db = records.Database("postgresql://*****:*****@localhost/testdb")
#Create database query string
db_query_string = "SELECT * FROM " + table_name + " WHERE uuid='" + uuid + "'"
try:
#Query the database
row = db.query(db_query_string)
#Convert result to JSON format and return
row_json = row.dataset.json
except Exception:
err_msg = "Query not successful."
err_json = {"Error": err_msg}
return err_json
#Convert to Python dictionary, row_json is array of length 1 with all data at index 0
result = json.loads(row_json)[0]
#Convert indexData field from json string to python dict
result["indexData"] = json.loads(result["indexData"])
return result
def __init__(self, storage: dict):
db_url = storage.get("DB_URL")
self.db = records.Database(db_url)
self.db.query("""CREATE TABLE IF NOT EXISTS `ToApi`(`url` VARCHAR(100),
`html` MEDIUMTEXT NOT NULL,PRIMARY KEY ( `url` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
)
def dump():
table_names = ['project_project']
db = records.Database(setting.DATABASE_URL)
for table_name in table_names:
dump_detail(db, table_name)
def ew_MQTT_m2m(as_topic, as_code):
is_decode_str = 'ERR: 没有找到那个消息'
i_topic = as_topic
is_in = as_code
is_t = i_topic.split('/', 2)[2]
is_vendor, is_model, is_ver, is_client = is_t.split('/', 4)
db = records.Database(mqtt_sql_s)
if is_vendor == 'ewide':
if is_model == 'smartpipe':
if is_ver == 'JV1':
list_ins = json.loads(is_in) # json读取
df_run = json_normalize(list_ins) # json 转 dataframe
# 通过编号得到ID
sql_pipe_id = "SELECT id FROM smartpipe_smartpipe where sn = '{}'".format(is_client)
rows = db.query(sql_pipe_id)
i_pipe_id = rows[0]['id'] # 设备ID
# 整理插入df
df_run["pipe_id_id"] = i_pipe_id
df_run1 = df_run[["pipe_id_id", "collection_time", "pressure", "ins_flow", "tem", "power"]] \
.to_json(orient="records", force_ascii=False)
# 转换list
list_run1 = json.loads(df_run1)
# 执行插入
t_sql = "insert smartpipe_pipedetail(pipe_id_id, collection_time,pressure, ins_flow, tem, power ) \
values (:pipe_id_id, :collection_time, :pressure, :ins_flow, :tem, :power) "
db.bulk_query(t_sql, list_run1)
return 'OK ewide smartpipe run'
return is_decode_str
def db_insert():
host = config.get_config_str('DATABASE', 'host')
# port = config.get_config_int('DATABASE', 'port')
port = config.get_config_str('DATABASE', 'port') #这里端口号因为是跟其他参数一个整体，都是str型
user = config.get_config_str('DATABASE', 'user')
password = config.get_config_str('DATABASE', 'password')
dbname = config.get_config_str('DATABASE', 'dbname')
charset = config.get_config_str('DATABASE', 'charset')
table = config.get_config_str('DATABASE', 'table')
# # 获取数据库
# db = records.Database('mysql+pymysql://root:@localhost:3306/dev01_git')
# db_url = 'mysql+pymysql://' + user + ':' + passwd + '@' + str(host) + ':' + str(port) + '/' + db
db_url = 'mysql+pymysql://' + user + ':' + password + '@' + host + ':' + port + '/' + dbname + '?' + 'charset' + '=' + charset
print(db_url)
# connect = records.Database('mysql+pymysql://用户名:密码@sqlURl:sql端号/库名')
# db = records.Database(db_url,connect_args={'charset' : 'utf8'})
db = records.Database(db_url)
# 查询
data = {'name': '李德涛', 'income': 6000}
# rows = db.query('select * from test_data where name = "李德涛";')
# sql = 'select * from '+ table + ' where name =: name and incom =: income',**data
# print(sql)
rows = db.query(
'select * from ' + table + ' where name =: name and incom =: income',
**data)
# print(rows.all().encode('utf-8').decode('unicode_escape'))
print(rows.all())
#导出为excel
xlsx_rows = rows.export('xlsx')
# xlsx = xlsx_rows.encode()
with open('test.xlsx', 'wb') as f:
f.write(xlsx_rows)
def db_read():
host = config.get_config_str('DATABASE', 'host')
# port = config.get_config_int('DATABASE', 'port')
port = config.get_config_str('DATABASE', 'port') #这里端口号因为是跟其他参数一个整体，都是str型
user = config.get_config_str('DATABASE', 'user')
password = config.get_config_str('DATABASE', 'password')
dbname = config.get_config_str('DATABASE', 'dbname')
charset = config.get_config_str('DATABASE', 'charset')
table = config.get_config_str('DATABASE', 'table')
# # 获取数据库
# db = records.Database('mysql+pymysql://root:@localhost:3306/dev01_git')
# db_url = 'mysql+pymysql://' + user + ':' + passwd + '@' + str(host) + ':' + str(port) + '/' + db
db_url = 'mysql+pymysql://' + user + ':' + password + '@' + host + ':' + port + '/' + dbname + '?' + 'charset' + '=' + charset
print(db_url)
# connect = records.Database('mysql+pymysql://用户名:密码@sqlURl:sql端号/库名')
# db = records.Database(db_url,connect_args={'charset' : 'utf8'})
db = records.Database(db_url)
# 查询
# rows = db.query('select * from test_data where name = "李德涛";')
sql1 = 'select * from ' + table + ';'
# sql = 'select * from '+ table + ' where name = "李德涛";'
print(sql1)
rows = db.query(sql1)
prin
def write_to_db(name, algotext, perf, parameters, start, end):
digest = hashlib.md5(algotext.encode("utf-8")).hexdigest()
db = records.Database(config['database']['url'])
db.query("""
insert into strategies (name, body, md5sum)
values (:name, :body, :checksum)
on conflict do nothing
""",
name=name,
body=algotext,
checksum=digest)
result = db.query(
"""
insert into test_runs
(strategy_id, securities, parameters, results, pickle, created_at, starts_at, ends_at)
values (
(select id from strategies where md5sum = :strategy_checksum),
:securities, :parameters, :results, :pickle_data, now(), :starts_at, :ends_at
) returning id
""",
strategy_checksum=digest,
securities=parameters['securities'],
parameters=json.dumps(parameters),
results=json.dumps({}),
pickle_data=pickle.dumps(perf),
starts_at=start,
ends_at=end,
)
run_id = result.all()[0].id
print("Test run saved as {run_id}".format(run_id=run_id))
def __init__(self):
tornado_settings = dict(
debug=False,
static_path=get_path('static'),
)
tornado.web.Application.__init__(self, url_handlers,
**tornado_settings)
# Mac 环境下 records.Database 创建数据库连接这一步会卡住，不知道什么原因
if sys.platform != 'darwin':
self.database = records.Database('sqlite:///' +
get_path('data/database.db'))
sql = "select name from sqlite_master where type='table' order by name"
with self.database.get_connection() as conn:
rows = conn.query(sql).as_dict()
name_set = set([t['name'] for t in rows])
if 'interceptor' not in name_set:
with self.database.get_connection() as conn:
conn.query(SQL_CREATE_TABLE_INTERCEPTOR)
if 'http_history' not in name_set:
with self.database.get_connection() as conn:
conn.query(SQL_CREATE_TABLE_HTTP_HISTORY)
for t in SQL_CREATE_INDEX_HTTP_HISTORY:
conn.query(t)
else:
self.database = None
def __init__(self):
# Config file where the informations are stocked
config_file = 'config.ini'
parser = ConfigParser()
parser.read(config_file)
section = 'mysql'
if section in parser:
# create the url to connect to the database
user = parser.get(section, 'user') + ':'
passwd = parser.get(section, 'passwd') + '@'
host = parser.get(section, 'host') + '/'
db = parser.get(section, 'db')
_connect = 'mysql+mysqldb://'
_connect += user
_connect += passwd
_connect += host
_connect += db
self._db = records.Database(_connect)
# Check if the databse is not empty
data = self._db.query('SELECT id_product FROM products LIMIT 1')
# If the database is empty, load the database
if data.first() is None:
success = self.update_database()
if success is False:
print("Une erreur s'est produite avec la base de données")
def get_leaderboard(limit=10, as_json=False):
"""
Get the leaderboard. Returns first 10 rows only!
champion = get_leaderboard()[0]['display_name'] -> top user
:param limit: Where to cut off the leaderboard (show the top <limit> users)
:return a list of Record types
"""
limit = int(limit)
with records.Database(DATABASE_URL) as db:
# This is a complex query (compared to the other stuff in here) but the
# gist is that it:
# - calculates the per-user sum of item scores
# - joins that to all the users in the database, using 0 for missing
# scores (so all users are represented)
# - sorts it so that the top scorer is first
# - limiting the result to :limit (by default 10) records.
rows = db.query('''
SELECT users.id, users.name, users.display_name,
COALESCE(it.score_sum, 0) AS score_sum
FROM users
LEFT JOIN (
SELECT SUM(items.score) As score_sum, items.deposited_by
FROM items
GROUP BY items.deposited_by
) AS it
ON it.deposited_by = users.id
ORDER BY score_sum DESC
LIMIT :limit;
''',
limit=limit)
return rows.export('json') if as_json else rows.all()
def database():
database = records.Database('sqlite://')
database.query("create table steps ( step_text text )")
database.query("insert into steps values ( 'Example step 0' )")
database.query("insert into steps values ( 'Example step 1' )")
database.query("insert into steps values ( 'Example step 2' )")
yield database
def create_user(can, name, display_name, phone_number, active=True):
"""
Create a new user.
:param can: CAN (compulsory)
:param name: Username (not compulsory)
:param display_name: Friendly name for display on UI (compulsory)
:param phone_number: Phone number for push notifs (not compulsory)
:param active: Is active (defaults to True)
:return: Id of new user or False if couldn't create
"""
params = {
'can': transform_can_to_canonical(can),
'name': str(name),
'display_name': str(display_name),
'phone_number': str(phone_number),
'active': bool(active)
}
with records.Database(DATABASE_URL) as db:
try:
db.query(
'''
INSERT INTO Users (can, name, display_name, phone_number, active)
VALUES (:can,:name,:display_name,:phone_number,:active);
''', **params)
# second round trip is terribly expensive but records is so cool...
return db.query('SELECT last_insert_id() AS id;').first()['id']
except IntegrityError as e:
print('IntegrityError! {}'.format(repr(e)))
return False
def get_roster_from_country():
country = request.args.get("team")
db = records.Database('sqlite:///wcdata/worldcupplayers.db')
players = db.query(
'''select matchid, playername, event, shirtnumber from worldcupplayers where teaminit = {}'''
.format("'" + country + "'")).all()
return players
def pg_conn():
# Gets environment variable DATABASE_URL from the pipenv .env file
# when the environment is activated with 'pipenv shell' or 'pipenv run'
DATABASE_URL = os.environ['DATABASE_URL']
db = records.Database(DATABASE_URL)
return db
def generate_from_tables(db_url,
destination,
sources,
qualify=False,
type_cast=False):
"""
Generates an `INSERT INTO... SELECT FROM` SQL statement.
Args:
db_url (str): Database URL in SQLAlchemy format
destination (str): Name of table to INSERT into
sources (list): Names of tables to select from, in order of preference
qualify (bool): Qualify column names with table name even if only one table
type_cast (bool): Cast values to destination data type where needed
Returns:
str: A SQL statement
"""
db = records.Database(db_url)
dest_column_block = []
source_column_block = []
# Each column should have only one source - sources listed first take
# precedence
source_columns = {}
for source in reversed(sources):
source_columns.update(
{col.column_name: col
for col in col_data(db, source)})
qualify = (len(sources) > 1) or qualify
for dest_col in col_data(db, destination):
dest_column_block.append(INDENT + dest_col.column_name)
source_col = source_columns.get(dest_col.column_name)
if source_col:
if qualify:
source_expr = '{}.{}'.format(source_col.table_name,
source_col.column_name)
else:
source_expr = source_col.column_name
else:
source_expr = no_value(db_url)
if type_cast and ((not source_col)
or source_col.data_type != dest_col.data_type):
source_expr = cast(source_expr,
new_type=dest_col.data_type,
db_url=db_url)
source_column_block.append('{}{}, -- ==> {}'.format(
INDENT, source_expr, dest_col.column_name))
dest_column_block = ',\n'.join(dest_column_block)
source_column_block = '\n'.join(source_column_block)
source_column_block = remove_last(source_column_block, ',')
from_clause = build_from_clause(sources)
return INSERT_TEMPLATE.format(**locals())
def get_data(db_url):
""" Returns data from the database """
logger.debug("Reading data from groople")
db = records.Database(db_url)
config_file = pathlib.Path(pathlib.Path(__file__).parent, "config.yml")
config = yaml.load(open(config_file, 'r'), Loader=Loader)
return groople.get_all_activities(db, config)
def movie():
if request.method == 'POST':
# get form data
m_title = request.form.get('title').lower()
m_year = request.form.get('year').lower()
m_genre = request.form.get('genre').lower()
m_description = request.form.get('description').lower()
m_rating = request.form.get('rating').lower()
print(m_title, m_year, m_genre, m_description, m_rating)
db = records.Database(DB) # connecting to database
print("Connected to DB")
tx = db.transaction() # init db transaction
try:
print("Adding record")
query = ('INSERT INTO movies (title, year, genre, '
'description, rating) VALUES(:title, :year, '
':genre, :description, :rating)')
print(query)
db.query(query, title=m_title, year=m_year, genre=m_genre,
description=m_description, rating=m_rating)
tx.commit() # commit changes
print("Success: record added")
except:
tx.rollback() # rollback if error adding data
print("Error adding record")
finally:
return redirect(url_for('index'))
else:
return render_template('add-movie.html')
def create(self):
"""Create schema."""
db = records.Database(
f"mysql+mysqlconnector://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:3306/")
db.query(f"CREATE SCHEMA IF NOT EXISTS `{self.db_name}` "
"DEFAULT CHARACTER SET utf8mb4;")
db.close()
def __init__(self, *args, **kwargs):
config = ReadConfig().getConfig()
self._dburl = "mysql+pymysql://" + config['mysql'][
'user'] + ":" + config['mysql']['passwd'] + "@" + config['mysql'][
'host'] + ":3306/" + config['mysql']['db'] + "?charset=utf8mb4"
self._conn = records.Database(self._dburl)
self._writeEngine = create_engine(self._dburl, echo=True)