def getInsertrecord(sourcedb, targetdb, tablename):
source_dbType, source_conn = DBConnect.getConnect(sourcedb)
target_dbType, target_conn = DBConnect.getConnect(targetdb)
pk_arr = getPKColumn(sourcedb)
tablename = str.upper(tablename)
if tablename in pk_arr.keys():
pk_cols = pk_arr[tablename]
log.info("Find table : %s primary key cols is %s", tablename, pk_cols)
source_cur = source_conn.cursor()
sql_get_source_data = "select %(cols)s from %(table)s" % {
'cols': pk_cols,
'table': tablename
}
source_cur.execute(sql_get_source_data)
while 1:
try:
eachline = source_cur.fetchone()
except:
continue
if eachline is None:
None
else:
None
None
else:
log.info("Can not find table : %s primary key...", tablename)
log.info("Script will use all cols to match the row")
None
def populate(self):
count_found=0;
count_unfound=0
for count in range(0,self.loops):
#Generate Video ID
random=RandomVideoId(0,11)
video_id = random.randomGenerator()
#Perform HTTP Request
http=HTTPRequest(video_id,0)
embed_info = http.httpGet()
#Connect and Insert Into DB if a Video Exists
#If a video is found
if embed_info!=404:
data = {"randa_id": video_id,
"date": datetime.datetime.utcnow()}
db=DBConnect("meteor")
inserted = db.connect().randa_youtube_videos.insert_one(data).inserted_id
count_found+=1
print video_id + " was inserted: " + str(count+1)
else:
print video_id + " does not exist: " + str(count+1)
count_unfound+=1
#Print Results of Script
print str(count_found) + " videos found and " + str(count_unfound) + " videos not found."
#Show Output of DB After
db=DBConnect("meteor")
cursor = db.connect().randa_youtube_videos.find()
for document in cursor:
print(document)
def sendMessage():
sUserName = request.POST.get('user')
sTextMessage = request.POST.get('text')
nUserId = getUserForMessage(sUserName)
dictMessage = {"text": str(sTextMessage)}
headers = {'content-type': 'application/json'}
jsonMessage = json.dumps(dictMessage)
print(jsonMessage)
urlSendMessage = 'https://botapi.tamtam.chat/messages?access_token=' + TOKEN + "&user_id=" + str(
nUserId)
nResponseCode = 0
while (nResponseCode != 200):
response = requests.post(urlSendMessage,
headers=headers,
data=jsonMessage,
verify=False)
sSender = getInfoBot()
nSenderId = sSender["user_id"]
sSenderName = sSender["username"]
nStatusCode = response.status_code
responseJson = response.json()
DBConnect.doInsertRecord(sSenderName, sUserName, str(sTextMessage),
json.dumps(dictMessage, ensure_ascii=False),
nStatusCode, responseJson, nSenderId, nUserId)
nResponseCode = DBConnect.getInfoMessageBD()
def addStackSuccessInfoToTask(session, taskId, accountName,
successInfoToAppendToTask):
# DBConnect ============================
# Update task['successInfo']
DBConnect.appendTaskSuccessInfo(taskId, accountName,
successInfoToAppendToTask)
# ======================================
return
def getAll(self):
db=DBConnect("meteor")
if self.medium==0:
cursor = db.connect().randa_youtube_videos.find()
elif self.medium==1:
cursor = db.connect().randa_vimeo_videos.find()
for document in cursor:
print(document)
def getAll(self):
db = DBConnect("meteor")
if self.medium == 0:
cursor = db.connect().randa_youtube_videos.find()
elif self.medium == 1:
cursor = db.connect().randa_vimeo_videos.find()
for document in cursor:
print(document)
def populate(self):
count_found = 0
count_unfound = 0
db = DBConnect("meteor")
connection = db.connect()
for count in range(0, self.loops):
#Generate Video ID
random = RandomVideoId(1, 8)
video_id = random.randomGenerator()
#Perform HTTP Request
http = HTTPRequest(video_id, 1)
embed_info = http.httpGet()
#Connect and Insert Into DB if a Video Exists
#If a video is found
key = connection.randa_vimeo_videos.count() + 1
if embed_info != 404:
data = {
"key": key,
"randa_id": video_id,
"date": datetime.datetime.utcnow()
}
try:
inserted = connection.randa_vimeo_videos.insert_one(
data).inserted_id
count_found += 1
print video_id + " was inserted: " + str(count + 1)
except pymongo.errors.DuplicateKeyError:
print video_id + "is already in the collection."
else:
print video_id + " does not exist: " + str(count + 1)
count_unfound += 1
#Print Results of Script
print str(count_found) + " videos found and " + str(
count_unfound) + " videos not found."
#Show Output of DB After
# db=DBConnect("meteor")
# cursor = db.connect().randa_vimeo_videos.find()
# for document in cursor:
# print(document)
#Debug Stuff To Use To Troubleshoot If Necessary
# EMPTY DATABASE
# db=DBConnect("meteor")
# db.connect().randa_videos.remove({})
#KNOWN VIDEO THAT WORKS
# http=HTTPRequest("D7Qgbs8RpN4")
# print(video_id)
# print(embed_info)
def getCommDB(srcauth, trgauth, tabname, tmptab, array=[]):
dbType, conn = DBConnect.getConnect(srcauth)
# 暂时只处理ORACLE
if dbType == 'ORACLE':
# 获取数据
col = ','.join(array)
cur = conn.cursor()
cur.execute('select %s from %s' % (col, tabname))
data = []
insert = {}
# :字段
value = []
c = []
commparelist = []
for j in array:
value.append(':%s' % j)
while True:
result = cur.fetchone()
if result is None:
sql = 'insert into %s(%s) values(%s)' % (tmptab, col,
','.join(value))
# print(sql)
RunSQL.dmlManySQL(DBConnect.getConnect(trgauth)[1], sql, data)
break
else:
for i in range(0, len(array)):
c.append("{colname} = '{value}'".format(colname=array[i],
value=result[i]))
insert[array[i]] = result[i]
cc = ' and '.join(c)
commpare = RunSQL.runSQL(
DBConnect.getConnect(srcauth)[1],
'select * from %s where %s' % (tabname, cc))
c = []
commparelist.append(commpare[0])
data.append(insert)
# print(data)
# 初始化
insert = {}
if len(data) == 1000:
sql = 'insert into %s(%s) values(%s)' % (tmptab, col,
','.join(value))
RunSQL.dmlManySQL(
DBConnect.getConnect(trgauth)[1], sql, data)
data = []
cur.close()
conn.close()
return commparelist
def addItem():
allItemsAdded = []
all_products = []
index_error = None
if request.method == 'GET':
"""clear session and display all items in product catalog"""
session.clear()
productcur = DBConnect.getAllProduct()
data = list(productcur)
all_products = list()
for row in data:
all_products.append(row)
return render_template('index.html', all_products=all_products)
elif request.method == 'POST':
allItemsAdded = []
""" obtain lst through stored session variable"""
allItemsAdded = session.get('sub_list', None)
button_value = str(request.form['submit'])
itemName = str(request.form['item'])
""" perform 2 important functionalities
1. Add item
2. Delte Item
List data structure used which is (allItemsAdded)
"""
if(button_value == 'add'):
if allItemsAdded is None:
allItemsAdded = []
if(DBConnect.checkItemExists(itemName)):
allItemsAdded.append(itemName)
else:
return "Sorry, Item cannot be added as it does not exist in our catalog. Please Enter the product code as mentioned in the catalof to swiftly add the item to your basket!"
elif(button_value == 'delete'):
## check DB catalog and delete
allItemsAdded.remove(itemName)
productcur = DBConnect.getAllProduct()
data = list(productcur)
all_products = list()
for row in data:
all_products.append(row)
session['sub_list'] = allItemsAdded
return render_template('index.html', all_products=all_products, lstOfItems = allItemsAdded)
def LoadConfig():
global logFilePath, bResponse, recvserverip, recvserverport, instrumentna, SchedulerTime_SerialCommunite, SchedulerTime_SendReagentToDB, SchedulerTime_SyncReagent, SchedulerTime_KeepAlive, bOS, os, inifile
global bInReagentAsync, bInSampleQueueCommand
global SyncReagentFromDB
recvserverip = GetSetting('DB', 'ServerIP')
recvserverport = GetSetting('DB', 'ServerPort')
instrumentna = GetSetting('MAIN', 'InstrumentName')
response = GetSetting('MAIN', 'NeedResponse')
if response == 'True':
bResponse = True
else:
bResponse = False
osv = GetSetting('MAIN', 'OS')
if osv == 'True':
bOS = True
else:
bOS = False
SyncReagentFromDB = GetSetting('SchedulerT', 'SyncReagentFromDB')
SchedulerTime_SerialCommunite = float(
GetSetting('SchedulerT', 'SerialComm'))
SchedulerTime_SendReagentToDB = float(
GetSetting('SchedulerT', 'SendReagentToDB'))
SchedulerTime_SyncReagent = float(GetSetting('SchedulerT', 'SyncReagent'))
SchedulerTime_KeepAlive = float(GetSetting('SchedulerT', 'KeepAlive'))
db.LoadConfig()
def MakeReagentMap(InstrumentName, TestMapDIC):
#sor.LoadConfig();
#instrumentname = sor.instrumentna;
reagentinfos = []
sql = "select distinct(reagent_name),reagent_type,reagent_lot from sys_info.reagent_detail where instrument_name = '" + InstrumentName + "' and onboard = 1 order by reagent_name"
results = db.FetchData(sql)
testmaphexcode = "01BF"
for row in results:
ReagentName = str(row[0])
if ReagentName in TestMapDIC:
print('1')
id = TestMapDIC[ReagentName]
print(id)
else:
id = row[2]
testmaphexcode += str_to_hex(id + ";" + ReagentName) + "FD"
reagenttype = row[1]
reagenttype = str(row[1]).upper()[0:1]
reagentcount = GetReagentCount(ReagentName, InstrumentName)
reagentinfo = "01B5" + str_to_hex(ReagentName) + "FD01" + str_to_hex(
row[2] + reagenttype + reagentcount + ";" + id) + "FD"
reagentinfo = "F0" + reagentinfo + checksum(reagentinfo) + "F8"
reagentinfos.append(reagentinfo)
sum = checksum(testmaphexcode)
testmaphexcode = "F0" + testmaphexcode + sum + "F8"
return testmaphexcode, reagentinfos
def InsertNewTransaction(self,transactionDate,amount,name):
try:
Connection = DBConnect()
db =Connection.create_connection()
db.open()
cur = db.cursor()
sql = ''' INSERT INTO [TRANSACTION](TRANSACTION_DATE,AMOUNT,NAME) VALUES(?,?,?) '''
transaction = (transactionDate,amount,name)
cur.execute(sql, transaction)
db.commit()
except Error as e:
print(e)
return None
return cur.lastrowid
def getTable(self):
connect = DBConnect.DBConnect(self.eiwit, self.jaartal)
connect.searchArtikels()
connect.compareArtikels()
self.tabelArt = connect.comparedArtikelSearch()
self.tabelProt = connect.searchProtein()
return
def GetReagentCount(ReagentName, InstrumentName):
sql = "select reagent_name,reagent_count from sys_info.reagent_detail where instrument_name = '" + InstrumentName + "' and onboard = 1 and reagent_name = '" + ReagentName + "' order by reagent_name"
results = db.FetchData(sql)
count = 0
for r in results:
count += int(r[1])
return str(count)
def CommTab(srcauth, trgauth, tabname, tmptab, array=[]):
data = []
for i in array:
c = 's.%s = t.%s' % (i, i)
data.append(c)
condition = ' and '.join(data)
sql = XmlUtil.dbExeSQL('TMP')
sqlformat = sql.format(srcname=tabname,
trgname=tmptab,
condition=condition)
flag = RunSQL.runSQL(DBConnect.getConnect(trgauth)[1], sqlformat)
# print (sqlformat)
if len(flag) == 0:
log.info('Compare Current compare data PK structure same in becompare')
log.info('Start check table attribute')
sql = XmlUtil.dbExeSQL('TRG_EXISTS')
sqlformat = sql.format(srcname=tabname,
trgname=tmptab,
condition=condition)
# print(sqlformat)
becompare = RunSQL.runSQL(DBConnect.getConnect(trgauth)[1], sqlformat)
# sql = XmlUtil.dbExeSQL('TRG_NOT_EXISTS')
# sqlformat = sql.format(srcname=srcname, trgname=trgname, condition=condition)
# print(sqlformat)
# becompare2 = RunSQL.runSQL(DBConnect.getConnect(trgauth)[1], sqlformat)
return [becompare, '']
else:
log.info(
'Compare Current compare data PK structure different in becompare ,%s PK more then %s'
% (tmptab, tabname))
# print(array, flag)
sql = XmlUtil.dbExeSQL('TRG_EXISTS')
sqlformat = sql.format(srcname=tabname,
trgname=tmptab,
condition=condition)
# print(sqlformat)
becompare = RunSQL.runSQL(DBConnect.getConnect(trgauth)[1], sqlformat)
cond = []
for i in range(0, len(array)):
c = "{key} = '{value}'".format(key=array[i], value=flag[0][i])
cond.append(c)
conds = ' and '.join(cond)
sql = 'select * from {tabname} where {condition}'.format(
tabname=tabname, condition=conds)
val_com = RunSQL.runSQL(DBConnect.getConnect(srcauth)[1], sql)
return [becompare, val_com]
def addItem():
allItemsAdded = []
all_products = []
index_error = None
if request.method == 'GET':
session.clear()
productcur = DBConnect.getAllProduct()
data = list(productcur)
all_products = list()
for row in data:
all_products.append(row)
return render_template('index.html', all_products=all_products)
elif request.method == 'POST':
allItemsAdded = []
allItemsAdded = session.get('sub_list', None)
button_value = str(request.form['submit'])
itemName = str(request.form['item'])
if (button_value == 'add'):
if allItemsAdded is None:
allItemsAdded = []
if (DBConnect.checkItemExists(itemName)):
allItemsAdded.append(itemName)
print("Here what the f**k exists :'(")
else:
return "Sorry, Item cannot be added as it does not exist in our catalog. Please Enter the product code as mentioned in the catalof to swiftly add the item to your basket!"
elif (button_value == 'delete'):
## check DB catalog and delete
allItemsAdded.remove(itemName)
productcur = DBConnect.getAllProduct()
data = list(productcur)
all_products = list()
for row in data:
all_products.append(row)
session['sub_list'] = allItemsAdded
return render_template('index.html',
all_products=all_products,
lstOfItems=allItemsAdded)
def InitDB(self, dbhost = '127.0.0.1', dbport = 3306, dbuser = '******', dbpwd = '', dbname = ''): """启动数据库操作对象。 返回：成功True，否则False""" self.__DbConnect = DBConnect.CConnector(dbhost, dbport, dbuser, dbpwd, dbname) self.__DbConnect.setDaemon(True) if not self.__DbConnect.Start(): return False self.VehicleData = CDBVehicleData(self.__DbConnect) return True
def main():
"""Declare variables"""
db = 'SoilSensors'
server = 'SQL DB IP'
uname = 'SQLDB USERNAME'
pwd = 'SQLDB PASSWORD'
FTPAdr = 'FTP IP'
FTPUn = 'FTP Uname'
FTPpwd = 'FTP Pass'
querySensors = "SELECT sensor FROM SoilSensors.Sensors"
queryReadings = "SELECT Sensors.sensor, Readings.reading, Readings.inserted \
Readings.sensor_id \
FROM SoilSensors.Readings INNER JOIN SoilSensors.Sensors \
ON Readings.sensor_id = Sensors.id;"
dirc = os.path.join('save dirc path')
UploadPath = os.path.join('//files//')
"""connect to Datasources to gather data"""
cnx = conct.CFSQLConnect(db, uname, pwd, server)
Data = cnx.queryMySQL(queryReadings)
sensors = cnx.queryMySQL(querySensors)
sensors = list(sensors.sensor.unique())
"""Produce plot and save to HTML file to display in HASS"""
files = []
truncate = "TRUNCATE TABLE SoilSensors.Plots;"
cnx.ExecuteMySQL(truncate)
for sensor in sensors:
fname = 'Sensor' + str(sensor) + 'Plot.html'
files.append(dirc + fname)
df = Data[Data.sensor == sensor]
plot=plotLine(df,'inserted','reading','Moisture',\
fname,'Datetime','Moisture Pct',dirc,'blue')
WritetoHTML(plot, fname, dirc)
"""Add URL to DB Table"""
sensor_id = Data[Data.sensor == sensor]
sensor_id = sensor_id.sensor_id.iloc[0]
URL = "http://HOST/" + fname
queryInsertURL = "INSERT INTO SoilSensors.Plots (sensor_id, sensor, URL) \
VALUES (%s, '%s', '%s')"
cnx.ExecuteMySQL(queryInsertURL % (sensor_id, sensor, URL))
"""connect to HASS Pi to upload plot via FTP"""
FTPObj = FTPConnt.FTPConnectMod(FTPAdr, FTPUn, FTPpwd)
FTPObj.UploadFile(fname, dirc + fname, UploadPath)
for file in files:
if os.path.exists(file):
os.remove(file)
"""Remove readings older then a month"""
query = "DELETE FROM SoilSensors.Readings WHERE \
inserted < (NOW() - INTERVAL 30 DAY);"
cnx.ExecuteMySQL(query)
def creTmpTab(auth, tabname, array=[]):
dbType, conn = DBConnect.getConnect(auth)
colarray = []
# 暂时只处理ORACLE
if dbType == 'ORACLE':
log.info('DB type is ORACLE, Start get columns')
sql = XmlUtil.dbExeSQL('COLUMN')
sqlformat = sql.format(tabname=tabname)
result = RunSQL.runSQL(conn, sqlformat)
# 数据处理
for i in array:
for j in result:
if i in j[0]:
colarray.append(j[0].split(',')[0])
crecol = ','.join(colarray)
tab = 'COMM_TMP'
sql = 'CREATE TABLE %s (%s)' % (tab, crecol)
result_cnt = RunSQL.runSQL(
DBConnect.getConnect(auth)[1],
"select count(1) from user_tables where table_name ='%s'" % tab)
if result_cnt[0][0] == 0:
log.info('%s not exists' % tab)
RunSQL.ddlSQL(DBConnect.getConnect(auth)[1], sql)
log.info('Create table successful')
else:
RunSQL.ddlSQL(
DBConnect.getConnect(auth)[1], 'delete from %s' % tab)
log.info('Delete table successful')
RunSQL.ddlSQL(DBConnect.getConnect(auth)[1], 'drop table %s' % tab)
log.info('Drop table successful')
RunSQL.ddlSQL(DBConnect.getConnect(auth)[1], sql)
log.info('Create table successful')
def getPKColumn(auth):
dbType, conn = DBConnect.getConnect(auth)
# 暂时只处理ORACLE
dir_pk = {}
if dbType == 'ORACLE':
log.info('DB type is ORACLE, Start get all primary key...')
sql = "select b.TABLE_NAME,listagg(b.COLUMN_NAME, ',') within group (order by b.POSITION) as pkcol from USER_CONSTRAINTS a,USER_CONS_COLUMNS b where a.TABLE_NAME = b.TABLE_NAME and a.CONSTRAINT_TYPE = \'P\' and a.STATUS = \'ENABLED\' group by b.table_name "
# 获取所有状态为enabled 的主键表名及主键字段
result = RunSQL.runSQL(conn, sql)
for arr in result:
dir_pk[arr[0]] = arr[1]
log.debug("All primary key is %s", dir_pk)
return dir_pk
def get_all_column(auth):
db_type, conn = DBConnect.getConnect(auth)
# 暂时只处理ORACLE
dir_all_cols = {}
if db_type == 'ORACLE':
log.info('DB type is ORACLE, Start get all primary key...')
sql = "select utc.table_name,listagg(utc.column_name,',') within group (order by utc.column_id) as columns from user_tab_cols utc group by utc.table_name"
# 获取所有状态为enabled 的主键表名及主键字段
result = RunSQL.runSQL(conn, sql)
for arr in result:
dir_all_cols[arr[0]] = arr[1]
log.debug("All primary key is %s", dir_all_cols)
return dir_all_cols
def main():
"""Declare variables"""
db = 'SoilSensors'
server = 'SQL DB IP'
uname = 'SQLDB USERNAME'
pwd = 'SQLDB PASSWORD'
querySensors = "SELECT id as sensor_id FROM SoilSensors.Sensors"
queryReadings = "SELECT Readings.reading, Readings.inserted \
FROM SoilSensors.Readings INNER JOIN SoilSensors.Sensors \
ON Readings.sensor_id = Sensors.id \
WHERE Sensors.id = %s \
ORDER BY Readings.inserted DESC limit 1;"
queryPlantFacts = "SELECT lastWatered, plant_id from SoilSensors.FactPlants \
WHERE sensor_id = %s"
queryPlants = "SELECT minSoilMoisture, maxSoilMoisture \
from SoilSensors.DimPlants WHERE id = %s"
updateQuery = "UPDATE SoilSensors.FactPlants SET lastWatered = NOW() \
WHERE sensor_id = %s AND plant_id = %s;"
PinQuery = "SELECT * FROM SoilSensors.Pins WHERE sensor_id = %s;"
"""connect to Datasources to gather data"""
cnx = conct.CFSQLConnect(db, uname, pwd, server)
sensors = cnx.queryMySQL(querySensors)
sensors = list(sensors.sensor_id)
"""Check each Sensors last moisture reading"""
for sensor in sensors:
plantFacts = cnx.queryMySQL(queryPlantFacts % (sensor))
Reading = cnx.queryMySQL(queryReadings % (sensor))
plant_id = plantFacts.plant_id.iloc[0]
lastWatered = plantFacts.lastWatered.iloc[0]
lastReading = Reading.inserted.iloc[0]
moisture = Reading.reading.iloc[0]
moistureMinMax = cnx.queryMySQL(queryPlants % (plant_id))
minMoisture = moistureMinMax.minSoilMoisture.iloc[0]
maxMoisture = moistureMinMax.maxSoilMoisture.iloc[0]
pin = cnx.queryMySQL(PinQuery % (sensor))
pin = pin.pin.iloc[0]
pin = int(pin)
relay = gpio.LED(pin, active_high=False)
if lastReading > lastWatered:
if (moisture <= minMoisture):
relay.on()
time.sleep(5)
relay.off()
cnx.ExecuteMySQL(updateQuery % (sensor, plant_id))
else:
sys.exit()
def createUniqueIndex(self):
db = DBConnect("meteor")
if self.medium == 0:
db.connect().randa_youtube_videos.create_index([("randa_id", 1)],
unique=True)
elif self.medium == 1:
db.connect().randa_vimeo_videos.create_index([("randa_id", 1)],
unique=True)
def getPKColumn(auth, tabname):
dbType, conn = DBConnect.getConnect(auth)
# 暂时只处理ORACLE
if dbType == 'ORACLE':
log.info('DB type is ORACLE, Start get constraints...')
sql = XmlUtil.dbExeSQL('CONSTRAINT')
sqlformat = sql.format(tabname=tabname)
result = RunSQL.runSQL(conn, sqlformat)
# 数据处理
array = []
for j in result:
detail = j[1].split(',')
for k in detail:
if 'P_' in k:
array.append(k.split('_')[1])
return array
def addItem(self,allItemsAdded):
self.dict1 = dict()
for pdCode in allItemsAdded:
productCode, productName, productPrice = DBConnect.getItems(pdCode)
#print("The details obtained here are: ", productCode, productName[0]['productName'], productPrice[0]['productPrice'])
productName = productName[0]['productName']
productPrice = productPrice[0]['productPrice']
itm = Item(productCode, productName, productPrice)
self.lstItm.append(itm)
self.totalPrice += productPrice
if productCode in self.dict1:
self.dict1[productCode] += 1
else:
self.dict1[productCode] = 1
print(self.dict1.viewitems())
print(self.dict1.viewvalues())
def columnComm(tabname, path):
log.info('Start to check table column name and type ..')
dbrange = ['SCOTT_10.45.15.201', 'SCOTT_10.45.15.205'] # 后期改成入参
dict = {}
for i in dbrange:
dbType, conn = DBConnect.getConnect(i)
# 暂时只处理ORACLE
if dbType == 'ORACLE':
log.info('DB type is ORACLE, Start get columns...')
sql = XmlUtil.dbExeSQL('COLUMN')
sqlformat = sql.format(tabname=tabname)
result = RunSQL.runSQL(conn, sqlformat)
# 数据处理
array = []
for j in result:
array.append(j[0])
dict[i] = array
# return dict
commpare(path, '字段', dict)
def constComm(tabname, path):
log.info('Start to check table constraint ..')
dbrange = ['SCOTT_10.45.15.201', 'SCOTT_10.45.15.205'] # 后期改成入参
dict = {}
for i in dbrange:
dbType, conn = DBConnect.getConnect(i)
# 暂时只处理ORACLE
if dbType == 'ORACLE':
log.info('DB type is ORACLE, Start get constraints...')
sql = XmlUtil.dbExeSQL('CONSTRAINT')
sqlformat = sql.format(tabname=tabname)
result = RunSQL.runSQL(conn, sqlformat)
# 数据处理
array = []
for j in result:
detail = j[0] + '@' + j[1]
array.append(detail)
dict[i] = array
# return dict
commpare(path, '约束', dict)
def schcoreboard():
dbconnection = DBConnect.DBConnect("localhost", "schnake", "schnake",
"schnake").connection
dbcursor = dbconnection.cursor()
query = ("SELECT * FROM scoreboard ORDER BY score_value DESC LIMIT 10")
dbcursor.execute(query)
largeText = pygame.font.Font('freesansbold.ttf', 30)
screen.fill(black)
for index in enumerate(dbcursor):
Surf, Rect = text_objects(str(index[1][1]), largeText)
Rect.centerx = ((width / 2) - 110)
Rect.centery = ((height / 11) * (1 + index[0]))
screen.blit(Surf, Rect)
Surf, Rect = text_objects("------", largeText)
Rect.centerx = ((width / 2))
Rect.centery = ((height / 11) * (1 + index[0]))
screen.blit(Surf, Rect)
Surf, Rect = text_objects(str(index[1][2]), largeText)
Rect.centerx = ((width / 2) + 100)
Rect.centery = ((height / 11) * (1 + index[0]))
screen.blit(Surf, Rect)
pygame.display.update()
dbcursor.close()
dbconnection.close()
enter = False
while enter is not True:
for event in pygame.event.get():
if event.type == pygame.QUIT:
pygame.quit()
quit()
elif event.type == KEYDOWN:
if event.key == 13:
enter = True
return None
def populate(self):
count_found = 0
count_unfound = 0
for count in range(0, self.loops):
#Generate Video ID
random = RandomVideoId(0, 11)
video_id = random.randomGenerator()
#Perform HTTP Request
http = HTTPRequest(video_id, 0)
embed_info = http.httpGet()
#Connect and Insert Into DB if a Video Exists
#If a video is found
if embed_info != 404:
data = {
"randa_id": video_id,
"date": datetime.datetime.utcnow()
}
db = DBConnect("meteor")
inserted = db.connect().randa_youtube_videos.insert_one(
data).inserted_id
count_found += 1
print video_id + " was inserted: " + str(count + 1)
else:
print video_id + " does not exist: " + str(count + 1)
count_unfound += 1
#Print Results of Script
print str(count_found) + " videos found and " + str(
count_unfound) + " videos not found."
#Show Output of DB After
db = DBConnect("meteor")
cursor = db.connect().randa_youtube_videos.find()
for document in cursor:
print(document)
def createUniqueIndex(self):
db=DBConnect("meteor")
if self.medium==0:
db.connect().randa_youtube_videos.create_index([("randa_id", 1)], unique=True)
elif self.medium==1:
db.connect().randa_vimeo_videos.create_index([("randa_id", 1)], unique=True)
def getCommDB(srcauth, trgauth, tabname, tmptab, array=[]):
dbType, conn = DBConnect.getConnect(srcauth)
col = []
sqlcol = "select column_name from user_tab_cols where table_name = '%s' order by column_id" % tabname
resultcol = RunSQL.runSQL(DBConnect.getConnect(srcauth)[1], sqlcol)
for i in resultcol:
col.append(i[0])
# 字段处理，PK在前
col = array + col
news_col = []
for cl in col:
if cl not in news_col:
news_col.append(cl)
# 暂时只处理ORACLE
if dbType == 'ORACLE':
# 获取数据
newcol = ','.join(news_col)
col = ','.join(array)
cur = conn.cursor()
cur.execute('select %s from %s' % (newcol, tabname))
data = []
insert = {}
# :字段
value = []
c = []
commparelist = []
for j in array:
value.append(':%s' % j)
while True:
result = cur.fetchone()
if result is None:
sql = 'insert into %s(%s) values(%s)' % (tmptab, col,
','.join(value))
# print(sql)
RunSQL.dmlManySQL(DBConnect.getConnect(trgauth)[1], sql, data)
becommparelist = CommTab(srcauth, trgauth, tabname, tmptab,
array)
commpare(TMP_PATH, type, commparelist, becommparelist)
# 重新初始化
sql = 'truncate table COMM_TMP'
RunSQL.ddlSQL(DBConnect.getConnect(trgauth)[1], sql)
commparelist = []
break
else:
for i in range(0, len(array)):
insert[array[i]] = result[i]
commparelist.append(result)
data.append(insert)
# print(data)
# 初始化
insert = {}
if len(data) == 10000:
sql = 'insert into %s(%s) values(%s)' % (tmptab, col,
','.join(value))
RunSQL.dmlManySQL(
DBConnect.getConnect(trgauth)[1], sql, data)
data = []
becommparelist = CommTab(srcauth, trgauth, tabname, tmptab,
array)
commpare(TMP_PATH, type, commparelist, becommparelist)
# 重新初始化
sql = 'truncate table COMM_TMP'
RunSQL.ddlSQL(DBConnect.getConnect(trgauth)[1], sql)
commparelist = []
cur.close()
conn.close()
return commparelist
def deleteAll(self):
db=DBConnect("meteor")
if self.medium==0:
db.connect().randa_youtube_videos.remove({})
elif self.medium==1:
db.connect().randa_vimeo_videos.remove({})
G_startYear = 1900 # annee de debut minimum par defaut pour tous les vecteurs
G_tableName = 'trade'
dirUse = G_path
dirLog = dirUse + '\\Log'
fileLog = open(dirLog + '\\createDBTradeTrbalRbal' + G_nomenclature + '.log',
'w')
def traitementFichierTXT(nomenclature, fileLog, tableName):
infoX = DBAccess.lectureNaceIndicatorData('x', nomenclature, tableName)
infoM = DBAccess.lectureNaceIndicatorData('m', nomenclature, tableName)
refDicX = infoX[0]
startYearX = infoX[1]
refDicM = infoM[0]
startYearM = infoM[1]
if startYearX != startYearM:
fileLog.write('Start year for indicators x and m are different.\n')
return
spiLibCreateTable.createTableTradeTrbalRbal(nomenclature, refDicX, refDicM,
startYearM, fileLog, tableName)
traitementFichierTXT(G_nomenclature, fileLog, G_tableName)
fileLog.close()
DBConnect.closeDB()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import DBConnect
import LogUtil
import os
import datetime
name = os.path.basename(__file__)
log = LogUtil.Logger(name)
source_dbType, source_conn = DBConnect.getConnect("sourceDB")
source_cur = source_conn.cursor()
target_dbType, target_conn = DBConnect.getConnect("targetDB")
# todo 表名先写死 后改成读配置文件
tableNames = ['SC']
# 数据比较阀值
dataCount = [50000, 60000, 6000]
# 获取表对应的字段
columns_sql = '''select utc.table_name,listagg(utc.column_name,',') within group (order by utc.column_id) as columns
from user_tab_cols utc
where utc.table_name in ('{tableNames}')
group by utc.table_name'''.format(tableNames="','".join(tableNames))
columns_results = source_cur.execute(columns_sql).fetchall()
# 获取表对应的主键
pk_sql = '''select b.table_name, listagg(b.column_name, ',') within group (order by b.position) as pkcol
from user_constraints a, user_cons_columns b
where a.table_name = b.table_name and a.constraint_type = 'P' and a.status = 'ENABLED' and a.constraint_name = b.constraint_name
from RandomVideoId import *
from DBConnect import *
from HTTPRequest import *
random=RandomVideoId()
print(random.randomGenerator())
db=DBConnect("meteor")
cursor = db.connect().randa_videos.find()
for document in cursor:
print(document)
http=HTTPRequest('D7Qgbs8RpN4')
embed_info = http.httpGet()
print(embed_info)
