def get_forex_price(prev_value, conn): # The function to get EUR/USD rate
try:
resp = req.get("https://webrates.truefx.com/rates/connect.html?f=html"
) # Requesting EUR/USD exchange rate
price = float(resp.text[53:69].replace("</td><td>", ""))
output = True # Output defines whether the forex data is availible and looking believable
# Sometimes it happens that the source of forex data outputs inadequate data. Let's put some measure what's the allowed range of the forex price.
if (price < 0.5) and (price > 2.0):
pconn = ibm_db_dbi.Connection(conn)
query = "select EUR_USD_REAL from crypto_db where EUR_USD_REAL > 0 limit 1"
price = pd.read_sql(query, pconn)["EUR_USD_REAL"][0]
output = False
# The same kind of protection against fake jumps
if ((price / prev_value < 0.9985) or
(price / prev_value > 1.0015)) and (prev_value != None):
pconn = ibm_db_dbi.Connection(conn)
query = "select EUR_USD_REAL from crypto_db where EUR_USD_REAL > 0 order by TIME_STAMP desc limit 1"
price = pd.read_sql(query, pconn)["EUR_USD_REAL"][0]
output = False
# Activated at the first itteration after launching the script
if (prev_value == None):
return price, True
# The forex data is not avaliable on weekends. In this case let's find the last avaliable value in the DB
except:
pconn = ibm_db_dbi.Connection(conn)
query = "select EUR_USD_REAL from crypto_db where EUR_USD_REAL > 0 order by TIME_STAMP desc limit 1"
price = pd.read_sql(query, pconn)["EUR_USD_REAL"][0]
output = False
return price, output
def confirm_closed_order(order_id, chatID, bot, conn, timestamp, pos_type,
close_price_actual, open_price_actual,
close_imbalance_actual, close_forex_actual):
if pos_type == "SELL EUR":
profit = -(close_price_actual -
open_price_actual) * 100 / open_price_actual
if pos_type == "BUY EUR":
profit = (close_price_actual -
open_price_actual) * 100 / open_price_actual
pconn = ibm_db_dbi.Connection(conn)
query = "update TRADE_HISTORY SET STATUS = 'CLOSED', "
query += "CLOSE_PRICE_ACTUAL = " + str(close_price_actual)
query += ", CLOSE_IMBALANCE_ACTUAL = " + str(close_imbalance_actual)
query += ", CLOSE_FOREX_ACTUAL = " + str(close_forex_actual)
query += ", PROFIT_ACTUAL = " + str(profit)
query += ", TIME_STAMP_CLOSE_ACTUAL = " + str(timestamp)
query += " where ORDER_ID_CLOSE = " + str(order_id)
ibm_db.exec_immediate(conn, query)
text = "Close order is filled. Profit: " + "% 1.3f" % profit + "%."
print(text)
bot.send_message(chatID, text)
query_2 = "update trade_history set DURATION_IN_MINS_ACTUAL = (TIME_STAMP_CLOSE_ACTUAL - TIME_STAMP_OPEN_ACTUAL) / 60"
ibm_db.exec_immediate(conn, query_2)
return None
def getAllCandidates(id):
try:
ibm_db_conn = ibm_db.connect(dsn, '', '')
conn = ibm_db_dbi.Connection(ibm_db_conn)
cursor = conn.cursor()
print("Connected to {0}".format(DB2_DB))
except:
print("Couldn't Connect to Database")
return False
try:
q1 = "SELECT * FROM JOB_" + str(id)
q1 = q1 + " ORDER BY OVERALL_SCORE DESC;"
cursor.execute(q1)
candyData = cursor.fetchall()
allCandy = []
for candy in candyData:
thisCandy = {
"id": candy[0],
"name": candy[1],
"email": candy[2],
"score": candy[-1]
}
allCandy.append(thisCandy)
except:
print('Error Querying Candidates')
ibm_db.close(ibm_db_conn)
print("connection closed")
return False
print('fetched candidates')
ibm_db.close(ibm_db_conn)
print("connection closed")
return allCandy
def SQL():
db = ibm_db.connect("username host password",'','')
conn = ibm_db_dbi.Connection(db)
cur = conn.cursor()
return cur,conn
def Employer():
if request.method=='POST':
conn.tables('DPS35835', 'EMPLOYER')
conn_str='database=BLUDB;hostname=dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net;port=50000;protocol=tcpip;uid=dps35835;pwd=PleaseGoCovid19@2020'
ibm_db_conn = ibm_db.connect(conn_str,'','')
conn = ibm_db_dbi.Connection(ibm_db_conn)
NAME = str(request.form.get('Name'))
USERNAME = str(request.form.get('Username'))
EMAIL_ID = str(request.form.get('Email'))
SECTOR_TYPE = str(request.form.get('sectType'))
SECTORNAME = str(request.form.get('SectName'))
COMPANY_NAME = str(request.form.get('CompName'))
POSITION = str(request.form.get('Position'))
COMPANY_SIZE = str(request.form.get('CompSize'))
# COMPANY_SIZE = int(COMPANY_SIZE)
PHONE = str(request.form.get('Phone'))
WEBSITE = str(request.form.get('Website'))
PASSWORD = str(request.form.get('Password'))
# entry=EMPLOYER(NAME = NAME, USERNAME = Us,PASSWORD=Password, COMPANY_NAME=CompName,COMPANY_SIZE=CompSize, WEBSITE=Website,POSITION=Position,SECTOR_TYPE=SectType,SECTORNAME=SectName)
insert = "insert into EMPLOYER values("+NAME+","+USERNAME+","+EMAIL_ID+","+PASSWORD+","+COMPANY_NAME+","+COMPANY_SIZE+","+POSITION+","+PHONE+","+SECTORNAME+","+SECTOR_TYPE+","+WEBSITE+")"
# params = (NAME,USERNAME,EMAIL_ID,PASSWORD,COMPANY_NAME,COMPANY_SIZE,POSITION,PHONE,SECTORNAME,SECTOR_TYPE,WEBSITE)
# print(params)
cur = conn.cursor()
cur.execute(insert)
stmt_insert = ibm_db.prepare(ibm_db_conn, insert)
ibm_db.execute(stmt_insert,params)
return render_template('Employer.html')
def dbconnector(options: dict):
"""Database DB2 connection helper
params
------
options : dict
Key-value pair of database connection settings.
Required:
|-- host
|-- port
|-- username
|-- password
`-- dbname
"""
host = options["host"]
port = options["port"]
username = options["username"]
password = options["password"]
dbname = options["dbname"]
dsn = "DRIVER={{IBM DB2 ODBC DRIVER}};" + \
"DATABASE="+ dbname +";" + \
"HOSTNAME="+ host +";" + \
"PORT="+ port +";" + \
"PROTOCOL=TCPIP;" + \
"UID="+ username +";" + \
"PWD="+ password +";"
hdbc = ibm_db.connect(dsn, "", "")
return ibm_db_dbi.Connection(hdbc)
def send_chart(message):
bio = BytesIO() #buffer for storing a figure w/o saving it in the disk
pconn = ibm_db_dbi.Connection(conn)
query = "select * from trade_history WHERE STATUS = 'CLOSED' order by TIME_STAMP_OPEN"
data = pd.read_sql(query, pconn)
datetime_start = datetime.fromtimestamp(min(data["TIME_STAMP_OPEN"]))
datetime_now = datetime.now()
delta = datetime_now - datetime_start
net_pnl = accumulative_list(data["PROFIT"])[-1]
est_pnl_pa = net_pnl * 31622400 / delta.total_seconds()
plt.plot(accumulative_list(data["PROFIT"]), "go-", linewidth=3.0, ms=10)
plt.ylabel('Net PnL (%)', fontsize=20)
plt.xlabel('Trades', fontsize=20)
plt.xticks(fontsize=20)
plt.yticks(fontsize=20)
plt.annotate("Start: " + str(datetime_start.strftime("%b %d %Y")) + "\n"
"Net PnL:" + "% 1.2f" % net_pnl + "%" + "\n"
"Est. Annual PnL:" + "% 1.2f" % est_pnl_pa + "%" + "\n",
xy=(0.02, 0.7),
xycoords='axes fraction',
fontsize=14)
plt.savefig(bio, bbox_inches='tight')
plt.clf()
bio.seek(0)
bot.send_photo(chatID, bio)
def dataPrep(input_arg):
if validators.url(input_arg):
faqs = Scrape_html(input_arg)
data_src = "URL"
else:
ext = os.path.splitext(input_arg)[-1].lower()
if ext in ['.pdf']:
faqs = Scrape_pdf(input_arg)
data_src = "PDF"
else:
faqs, data_src = Scrape_sheet(input_arg)
print("Extracted",len(faqs),"Q/A from",data_src,"source")
print("Connecting to Database...")
ibm_db_conn = ibm_db.connect("DATABASE="+"BLUDB"+";HOSTNAME="+"dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net"+";PORT="+"50000"+";PROTOCOL=TCPIP;UID="+"dnr61151"+";PWD="+"1l2mz7b+hclkfnj0"+";", "","")
conn = ibm_db_dbi.Connection(ibm_db_conn)
print("\nPushing to Database...\n")
table_name = "FAQ_DATAPREP"
for faq in faqs:
query = "INSERT INTO " + table_name + " VALUES('"+faq[0]+"','"+faq[1]+"',"
if faq[2] is None:
query += "NULL,"
else:
query += "'"+faq[2]+"',"
if faq[3] is None:
query += "NULL,"
else:
query += "'"+faq[3]+"',"
query += "'"+faq[4]+"')"
stmt = ibm_db.exec_immediate(ibm_db_conn, query)
print("Data Ingested to", table_name)
return "Success"
class dbistorage:
import sys # Provides Information About Python Interpreter Constants And Functions
import ibm_db # Contains The APIs Needed To Work With Db2 Databases
import ibm_db_dbi
conn = ibm_db.connect(
"DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=arquant;",
"", "")
connection = ibm_db_dbi.Connection(conn)
tab = "CREATE TABLE Ordering6 (Price float, Amount int, ID VARCHAR(60), Side int, Type int)"
#return1 = ibm_db.exec_immediate(conn, tab)
def dbistore(self, order):
params = ()
insert = "insert into ordering6 values(?,?,?,?,?)"
stmt_insert = self.ibm_db.prepare(self.conn, insert)
for i in range(len(order)):
if i == 0:
aux1 = [(order[i].Price, order[i].Amount, order[i].ID,
order[i].Side, order[i].Type)]
else:
aux2 = (order[i].Price, order[i].Amount, order[i].ID,
order[i].Side, order[i].Type)
aux1.append(aux2)
#print(aux1)
#params=(params, (order[i].Price, order[i].Amount, order[i].ID, order[i].Side, order[i].Type))
params = tuple(aux1)
#print(params)
self.ibm_db.execute_many(stmt_insert, params)
select = "select price, amount, id, side, type from ordering6"
cur = self.connection.cursor()
cur.execute(select)
row = cur.fetchall()
for j in range(len(order)):
print("{} \t ".format(row[j]), end="\n")
def col_to_row_organize(input_table_name, conn_str):
split = input_table_name.split(".")
if len(split) > 1:
schema = split[0] + "."
table_name = split[1]
else:
table_name = split[0]
schema = ""
ibm_db_conn = ibm_db.connect(conn_str, "", "")
connection = ibm_db_dbi.Connection(ibm_db_conn)
new_table_name = table_name + "_ROW"
# make new row based table with this schema
sql = "CREATE TABLE " + schema + new_table_name + " LIKE " + schema + table_name + " ORGANIZE BY ROW;"
ibm_db.exec_immediate(ibm_db_conn, sql)
# copy values from original table into new rowbased table
sql = "INSERT INTO " + schema + new_table_name + " SELECT * FROM " + schema + table_name + ";"
ibm_db.exec_immediate(ibm_db_conn, sql)
#
sql = "DROP TABLE " + schema + table_name + ";"
ibm_db.exec_immediate(ibm_db_conn, sql)
sql = "RENAME " + schema + new_table_name + " To " + table_name + ";"
ibm_db.exec_immediate(ibm_db_conn, sql)
return None
def main(args):
ibm_db_conn = ibm_db.connect(
"DATABASE=" + "BLUDB" + ";HOSTNAME=" +
"dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net" + ";PORT=" +
"50000" + ";PROTOCOL=TCPIP;UID=" + "dnr61151" + ";PWD=" +
"1l2mz7b+hclkfnj0" + ";", "", "")
conn = ibm_db_dbi.Connection(ibm_db_conn)
faqs = []
faqs = [
('What is coronavirus?',
'Coronaviruses are a large family of viruses that are known to cause illness ranging from the common cold to more severe diseases such as Middle East Respiratory Syndrome (MERS) and Severe Acute Respiratory Syndrome (SARS)'
),
('Can you tell me about coronavirus?',
'Coronaviruses are a large family of viruses that are known to cause illness ranging from the common cold to more severe diseases such as Middle East Respiratory Syndrome (MERS) and Severe Acute Respiratory Syndrome (SARS)'
),
('Coronavirus',
'Coronaviruses are a large family of viruses that are known to cause illness ranging from the common cold to more severe diseases such as Middle East Respiratory Syndrome (MERS) and Severe Acute Respiratory Syndrome (SARS)'
)
]
print("\nPushing to Database...\n")
table_name = "COVIDFAQ"
for ques, ans in faqs:
query = "INSERT INTO " + table_name + " VALUES('" + ques + "','" + ans + "')"
stmt = ibm_db.exec_immediate(ibm_db_conn, query)
print("Data Ingested to", table_name)
return ({"message": "success"})
def get_db_connection(db_type, user, password, host, port, db_name):
""" Connects to the database.
Parameters
----------
db_type : str
The database type
user : str
The database user
password : str
The database user password
host : str
The database host or ip address
port : str
The port to connect to
db_name : str
The database or service name
Returns
-------
conn
A database connection
"""
try:
if db_type == DBType.ORACLE.value:
import cx_Oracle
return cx_Oracle.connect(user, password,
host + ":" + port + "/" + db_name)
elif db_type == DBType.MYSQL.value:
import mysql.connector
return mysql.connector.connect(user=user,
password=password,
host=host,
port=int(port),
database=db_name)
elif db_type == DBType.POSTGRES.value:
import psycopg2
return psycopg2.connect("""user='******'
password='******'
host='{2}'
port='{3}'
dbname='{4}'""".format(
user, password, host, port, db_name))
elif db_type == DBType.DB2.value:
import ibm_db
import ibm_db_dbi
conn = ibm_db.connect(
"UID={0};PWD={1};HOSTNAME={2};PORT={3};DATABASE={4};".format(
user, password, host, port, db_name), "", "")
return ibm_db_dbi.Connection(conn)
else:
raise ValueError(
"Database type '{0}' is not supported.".format(db_type))
except ModuleNotFoundError as err:
raise ConnectionError(
"Database driver module is not installed: {0}. Please install it first."
.format(str(err)))
def get_all_events():
if __db2_connection__ is not None:
dbi_conn = ibm_db_dbi.Connection(__db2_connection__)
events_dataframe = pd.read_sql(
"SELECT id,description,status FROM eventsdb.events", dbi_conn)
return (len(events_dataframe),
events_dataframe.memory_usage(index=True).sum())
def print_users():
select_query = "SELECT ID, USERNAME, LAST_LOGIN FROM USERS"
pandas_conn = ibm_db_dbi.Connection(conn)
dataframe = (pd.read_sql(select_query,
pandas_conn).to_string()).split('\n')
print()
for line in dataframe:
print(f"{line}".center(99))
def connect(_db, _c):
"""the actual connect"""
import ibm_db_dbi
LOGGER.debug("Connecting %s as %s",
connect_string(_c).replace(_c['password'], '*X*X*X*'), 'user')
con = _db.pconnect(connect_string(_c), "", "")
return ibm_db_dbi.Connection(con)
def connect():
global con,conn
try:
con=idb.connect("DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-10.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=tgz17976;PWD=3tc5kj75xb-qnzj9",'','')
conn = dbi.Connection(con)
except:
print("Error in connection, sqlstate = ")
errorMsg = idb.conn_errormsg()
print(errorMsg)
def db2_connect(database, host, port, username, password):
database = "DATABASE=%s;" % database
host = "HOSTNAME=%s;" % host
port = "PORT=%s;" % port
username = "******" % username
password = "******" % password
conn_string = database + host + port + 'PROTOCOL=TCPIP;' + username + password
print(conn_string)
connection = ibm_db.connect(conn_string, '', '')
return ibm_db_dbi.Connection(connection)
def send_last5(message):
pconn = ibm_db_dbi.Connection(conn)
query = "select * from trade_history where STATUS = 'CLOSED' order by TIME_STAMP_CLOSE desc LIMIT 5"
data = pd.read_sql(query, pconn)
text = "Last 5 completed trades:\n"
text += "Opened / PnL / Duration \n"
for i in range(len(data)):
text += last_trades_text(data, i)
bot.reply_to(message, text)
def add_job(jobData):
try:
ibm_db_conn = ibm_db.connect(dsn, '', '')
conn = ibm_db_dbi.Connection(ibm_db_conn)
cursor = conn.cursor()
print("Connected to {0}".format(DB2_DB))
except:
print("Couldn't Connect to Database")
return False
# try:
cursor.execute("SELECT count(*) FROM JOBS_RAW;")
jobCount = int(cursor.fetchall()[0][0])
print("No. of current Job Postings: ", jobCount)
q1 = "INSERT INTO JOBS_RAW (JOBID, JOBROLE, JOBLOC, JOBDESC, JOBYOE, JOBYOE_MUL, JOBSKILLS, JOBSKILLS_MUL, GITID, GIT_MUL, TWEETID, BIG5_MUL, VALUES_MUL, SELF_DESC, SELF_DESC_MUL, JOB_WANT_WHY, JOB_WANT_WHY_MUL, JOB_REQ_WHAT, JOB_REQ_WHAT_MUL, PASSION, PASSION_MUL, APT_MUL, DATE_JOIN, DATE_JOIN_MUL)"
q1 = q1 + " VALUES ({0}, '{1}', '{2}', '{3}', {4}, {5}, '{6}', {7}, '{8}', {9}, '{10}', {11}, {12}, '{13}', {14}, '{15}', {16}, '{17}', {18}, '{19}', {20}, {21}, '{22}', {23});".format(
jobCount + 1, jobData['jobrole'], jobData['location'],
jobData['description'], jobData['yoe'], jobData['yoe_mul'],
jobData['jobskills'], jobData['jobskills_mul'], "mihirs16",
jobData['gitId_mul'], "@cached_cadet", jobData['big5_mul'],
jobData['values_mul'], jobData['self_desc'], jobData['self_desc_mul'],
jobData['job_want_why'], jobData['job_want_why_mul'],
jobData['job_req_what'], jobData['job_req_what_mul'],
jobData['passion'], jobData['passion_mul'], jobData['apt_mul'],
datetime.datetime.strptime(jobData['date_join'],
'%m-%d-%y'), jobData['date_join_mul'])
# print (q1)
cursor.execute(q1)
cursor.execute("SELECT * FROM JOBS_RAW;")
for r in cursor.fetchall():
print(r)
# except:
# print ("JOBS_RAW Query Error!")
# ibm_db.close(ibm_db_conn)
# print ("connection closed")
# return False
try:
q1 = "CREATE TABLE JOB_" + str(jobCount + 1)
q1 = q1 + " (CANDY_ID INT, CNAME VARCHAR(50), EMAIL VARCHAR(30), GITID VARCHAR(30), TWEETID VARCHAR(30), YOE INT, SKILLS VARCHAR(128), SELF_DESC VARCHAR(256), JOB_WANT_WHY VARCHAR(256), JOB_REQ_WHAT VARCHAR(256), PASSION VARCHAR(256), APT FLOAT, DATE_JOIN DATE, OVERALL_SCORE FLOAT);"
cursor.execute(q1)
cursor.execute("SELECT * FROM JOB_" + str(jobCount + 1) + ";")
for r in cursor.fetchall():
print(r)
except:
print("JOB_" + str(jobCount + 1) + " Query Error!")
ibm_db.close(ibm_db_conn)
print("connection closed")
return False
ibm_db.close(ibm_db_conn)
print('job added succesfully!')
print("connection closed")
return jobCount + 1
def main():
logger.info("Starting DB2 extractor...")
project_id = os.getenv("GCP_PROJECT")
secret_id = os.getenv("SECRET_ID")
db_properties = access_secret_version(project_id, secret_id)
db_properties = '[db]\n' + db_properties
config = configparser.RawConfigParser()
config.read_string(db_properties)
source_db_host = config.get("db", "source-host")
source_db_user = config.get("db", "source-user")
source_db_pass = config.get("db", "source-password")
source_db_schema = config.get("db", "source-schema")
source_conn = db_utils.connect_db(source_db_host, source_db_user,
source_db_pass)
conn = ibm_db_dbi.Connection(source_conn)
bquery_dataset = 'db2-data'
bquery_dataset = bquery_dataset.replace("-", "_")
for table_to_export in tables_to_export:
table = table_to_export["table"]
source_schema_table = "{}.{}".format(source_db_schema, table)
columns = table_to_export["columns"]
where_clause = table_to_export["where_clause"]
bquery_table_schema = table_to_export["schema_bigquery"]
sql = "SELECT {} FROM {}".format(columns, source_schema_table)
if where_clause:
sql = "{} WHERE {}".format(sql, where_clause)
logger.info("Running sql: %s", sql)
conn = ibm_db_dbi.Connection(source_conn)
# pandas dataframe with the results
df = pd.read_sql(sql, conn)
logger.info("Number of rows in dataframe: %s", df.shape[0])
response = upload_to_bigquery(df, table.lower(), bquery_dataset,
bquery_table_schema)
return response
def query(self, sql, file, origem, tipo=0):
global progresso
global texto
config = ConfigParser()
config.read('conf/pcom.ini')
dbase = config.get("DB", "database")
host = config.get("DB", "hostname")
port = config.get("DB", "port")
protocol = config.get("DB", "protocol")
uid = config.get("DB", "uid")
pwd = config.get("DB", "pwd")
conexao = 'DATABASE = ' + dbase + ';HOSTNAME=' + host + ';PORT=' + port + ';PROTOCOL=' + protocol + ';UID=' + uid + ';PWD=' + pwd + ';'
print(conexao)
ibm_db_conn = ibm_db.connect(conexao, "", "")
conn = ibm_db_dbi.Connection(ibm_db_conn)
lista = []
try:
cursor = conn.cursor()
if tipo == 0:
f = open(sql, 'r', encoding='latin-1')
query = " ".join(f.readlines())
else:
query = sql
cursor.execute(query)
curs = cursor.fetchall()
for k in cursor.description:
lista.append(k[0])
if len(curs) >= 1:
txt = open(f'{origem}\\{file}' + str('.csv'),
'w+',
encoding='UTF-8')
txt.write((','.join(lista) + '\n').replace(',', ';'))
for i, x in enumerate(curs):
progresso = (int(100.0 / float(len(curs)) * float(i)))
lista = []
for n, y in enumerate(x):
lista.append(str(y).replace(',', '-'))
txt.write((','.join(lista) + '\n').replace(',', ';'))
except Exception as e:
print('Não foi possível executar | ', e)
progresso = False
texto = str(e)
finally:
texto = file, origem
return texto
def connect_to_db():
db = config.get('db2', 'db')
hostname = config.get('db2', 'hostname')
port = config.get('db2', 'port')
user = config.get('db2', 'username')
pw = config.get('db2', 'password')
connect_string = "DATABASE=" + db + ";HOSTNAME=" + hostname + ";PORT=" + port + ";UID=" + user + ";PWD=" + pw + ";"
db2conn = ibm_db.connect(connect_string, "", "")
conn = ibm_db_dbi.Connection(db2conn)
cursor = conn.cursor()
return cursor
def conn_db2(user, pw, host, port, database):
""" Connect to the database. """
conn_str = 'DATABASE=' + {database} + '; HOSTNAME=' + {
host
} + '; PORT=' + {port} + '; PROTOCOL=TCPIP; UID=' + {user} + '; PWD=' + {
pw
} + ';'
try:
return ibm_db_dbi.Connection(ibm_db.connect(conn_str, '', ''))
except Exception:
print(f"Failed connect: {ibm_db.conn_errormsg()}")
raise
def confirm_opened_order(order_id, chatID, bot, conn, open_price_actual,
open_imbalance_actual, open_forex_actual, timestamp):
pconn = ibm_db_dbi.Connection(conn)
query = "update TRADE_HISTORY SET STATUS = 'OPEN', "
query += "OPEN_PRICE_ACTUAL = " + str(open_price_actual)
query += ", OPEN_IMBALANCE_ACTUAL = " + str(open_imbalance_actual)
query += ", OPEN_FOREX_ACTUAL = " + str(open_forex_actual)
query += ", TIME_STAMP_OPEN_ACTUAL = " + str(timestamp)
query += " where ORDER_ID_OPEN = " + str(order_id)
ibm_db.exec_immediate(conn, query)
text = "Open order is filled. \n" + "Open price: " + "% 1.4f" % open_price_actual + ", imbalance: " + "% 1.2f " % open_imbalance_actual + "%."
print(text)
bot.send_message(chatID, text)
return None
def connect_to_db2(self):
"""
Connect to DB2 and get the instance of the handler.
:return: Tuple of DB2 connection instances
"""
db2_conn_info = f"DATABASE={self.payload['db2_database']};" +\
f"HOSTNAME={self.payload['db2_host_name']};" +\
f"PORT={self.payload['db2_port']};" +\
f"PROTOCOL={self.payload['db2_protocol']};" +\
f"UID={self.payload['db2_uid']};" +\
f"PWD={self.payload['db2_password']};" +\
f"Security={self.payload['db2_security']};"
ibm_db_conn = db2.connect(db2_conn_info, '', '')
conn = ibm_db_dbi.Connection(ibm_db_conn)
return ibm_db_conn, conn
def new_announcement(path):
conn=ibm_db.connect("DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=hsw38568;PWD=kqx814-tqfxn1zhw",'','')
pconn=ibm_db_dbi.Connection(conn)
dataframe=pd.read_sql("SELECT * FROM USER_DATA ",pconn)
unq_id=[]
for i,unqe_id in enumerate(dataframe['USER_ID']):
if unqe_id not in unq_id:
unq_id.append(unqe_id)
doc = open(path, 'rb')
for i,chat_id in enumerate(unq_id):
bot.send_document(chat_id, doc)
def connect_to_schema(schema, conn_str):
"""Connect to a particular DB schema.
Input: schema - name of schema in Db2 to connect to
conn_str - a Db2 connection string
Output: none
"""
ibm_db_conn = ibm_db.connect(conn_str, "", "")
conn = ibm_db_dbi.Connection(ibm_db_conn)
sql = "set schema " + schema
stmt = ibm_db.exec_immediate(ibm_db_conn, sql)
return ibm_db_conn, conn
def __init__(self, iuid=None, ipwd=None, idb2='*LOCAL', ictl='*here *cdata', ipc='*na', isiz=512000, ilib=None):
if hasattr(iuid, 'cursor'):
# iuid is a PEP-249 connection object, just store it
self.conn = iuid
elif isinstance(iuid, ibm_db.IBM_DBConnection):
# iuid is a ibm_db connection object, wrap it in a ibm_db_dbi connection object
self.conn = ibm_db_dbi.Connection(iuid)
else:
# user id and password passed, connect using ibm_db_dbi
ipwd = ipwd if ipwd else os.getenv('PASSWORD', None)
self.conn = ibm_db_dbi.connect(database=idb2, user=iuid, password=ipwd)
self.ctl = ictl
self.ipc = ipc
self.siz = isiz
self.lib = ilib if ilib else os.getenv('XMLSERVICE', 'QXMLSERV')
def mars_db_conn():
"""Returns a JDBC connections to MarsDB"""
user, pwd = mars_db_credentials()
mars_prod = connect(
'DATABASE=D2CPROD;'
'HOSTNAME=11.48.22.142;' # 127.0.0.1 or localhost works if it's local
'PORT=50050;'
'PROTOCOL=TCPIP;'
'UID=%s;'
'PWD=%s;' % (user, pwd),
'',
'')
return ibm_db_dbi.Connection(mars_prod)
def dbConnect(self, orig):
# DB CONNECT
self.db = ibm_db.connect(
"Server=127.0.0.1;Port=48000;Hostname=*****;Database=MEEKNESS;UID=***;PWD=***;",
"", "")
self.conn = ibm_db_dbi.Connection(self.db)
self.cur = self.conn.cursor()
# RETRIEVE ALL CHARACTERS
sql = """SELECT c.Character
FROM Characters c
ORDER BY c.Character;"""
self.cur.execute(sql)
self.charlist = []
for row in self.cur.fetchall():
self.charlist.append(row[0])
# RETRIEVE CHARACTER NAME AND PICTURE BLOB
sql = """SELECT c.Character, c.Picture, c.Description
FROM Characters c
WHERE c.Character = ?"""
self.cur.execute(sql, (orig, ))
for row in self.cur.fetchall():
self.meek_char = row[0]
self.pic_data = row[1]
self.desc = row[2]
# RETRIEVE CHARACTER's QUALITIES
sql = """SELECT q.Quality
FROM Characters c
INNER JOIN Qualities q ON c.ID = q.CharacterID
WHERE c.Character = ?"""
self.cur.execute(sql, (orig, ))
self.qual_list = []
for row in self.cur.fetchall():
self.qual_list.append(row[0])
self.cur.close()
self.conn.close()