def create_index2(self, sqlcmd, index_name):
"""创建索引"""
# check the index
sqlstr = """select * from pg_indexes
where indexname = %s
"""
try:
# search the index by name
self.pgcur2.execute(sqlstr, (index_name,))
if self.pgcur2.rowcount == 1 : # 索引已经存在
#rdb_log.log('DataBase', 'Has been existed index "' + index_name + '"', 'info')
return 0
else:
try:
self.pgcur2.execute(sqlcmd)
self.conn2.commit()
#rdb_log.log('DataBase', 'Created index "' + index_name + '"', 'info')
#
sqlstr = "select tablename from pg_indexes where indexname = %s"
self.pgcur2.execute(sqlstr, (index_name,))
table_name = self.pgcur2.fetchone()[0]
self.analyze(table_name)
return 0
except:
rdb_log.log('DataBase', 'Created index ' + index_name + ' failed :' + sqlcmd, 'error')
raise
except:
rdb_log.log('DataBase', 'Created index ' + index_name + ' failed :' + sqlcmd, 'error')
raise
def createtalbe(self, sqlcmd):
try:
self.pgcur2.execute(sqlcmd)
return 0
except:
rdb_log.log('DataBase', 'Create Table Failed' + sqlcmd, 'info')
raise
def execute1(self, sqlcmd, parameters = []):
'''往目标数据库表插入数据'''
try:
self.pgcur1.execute(sqlcmd, parameters)
return 0
except Exception,ex:
print '%s:%s' % (Exception, ex)
rdb_log.log('DataBase', 'SQL excute error:' + sqlcmd, 'info')
raise
def query_fromcur2(self, sqlcmd):
'''在源数据库表查询数据'''
try:
self.pgcur2.execute(sqlcmd)
return 0
except Exception,ex:
print '%s:%s' % (Exception, ex)
rdb_log.log('DataBase', 'SQL query error:' + sqlcmd, 'info')
raise
def CreateIndex1_ByName(self, index_name):
if self.connected1 != True:
rdb_log.log('DataBase', "Connect1 is not opened.", 'error')
return -1
sqlcmd = self.SqlScriprt.GetSqlScript(index_name)
if sqlcmd == None:
#rdb_log.log('DataBase', "Doesn't exist SQL script for index " + index_name, 'error')
return -1
return self.create_index1(sqlcmd, index_name)
def callproc(self, proc_name, parameters = []):
'''调用目标数据库的存储过程'''
try:
self.pgcur2.callproc(proc_name, parameters)
self.recordno += 1
return 0
except Exception,ex:
print '%s:%s' % (Exception, ex)
rdb_log.log('DataBase', 'call process error:' + proc_name, 'info')
raise
def CreateTable1_ByName(self, table_name):
if self.connected1 != True:
rdb_log.log('DataBase', "Connect1 is not opened.", 'error')
return -1
sqlcmd = self.SqlScriprt.GetSqlScript(table_name)
if sqlcmd == None:
rdb_log.log('DataBase', "Doesn't exist SQL script for table " + table_name, 'error')
return -1
return self.CreateTalbe1(sqlcmd, table_name)
def deletetabledata1(self, table_name):
'''删除目标数据库表中的数据'''
sqlcmd = "delete from " + table_name
try:
self.pgcur1.execute(sqlcmd)
self.conn1.commit()
return 0
except Exception,ex:
print '%s:%s' % (Exception, ex)
#raise Exception, 'database operate wrong'
rdb_log.log('DataBase', 'SQL deletetabledata error:' + sqlcmd, 'error')
raise
def execute(self, sqlcmd, parameters = []):
'''往目标数据库表插入数据'''
try:
self.pgcur2.execute(sqlcmd, parameters)
self.recordno += 1
if self.recordno % 100000 == 0:
self.commit()
return 0
except Exception,ex:
print '%s:%s' % (Exception, ex)
rdb_log.log('DataBase', 'SQL excute error:' + sqlcmd, 'info')
raise
def readlines(path):
lines = []
file_object = open(path, 'r')
try:
lines = file_object.readlines()
except:
file_object.close()
rdb_log.log('DataBase', "Doesn't exist file DataBasePath.txt")
exit(1)
finally:
file_object.close()
return lines
def CreateDictionaryTable(self, table_name):
if table_name == None or table_name == '':
return 0
if self.connected2 != True:
rdb_log.log('DataBase', "Connect2 is not opened.", 'error')
return -1
sqlcmd = self.SqlScriprt.GetSqlScript('rdb_name_dictionary')
if sqlcmd == None:
rdb_log.log('DataBase', "Doesn't exist SQL script for table " + table_name, 'error')
raise
else:
# 把“rdb_name_dictionary”， 替换成“rdb_name_dictionary_后缀。”
sqlcmd = sqlcmd.replace('rdb_name_dictionary', table_name)
sqlstr = '''
SELECT tablename
FROM pg_tables
where tablename = %s
'''
if self.execute2(sqlstr, (table_name,)) == -1:
rdb_log.log('DataBase', 'Search table ' + table_name + ' failed :' + sqlstr, 'error')
return -1
else:
if self.pgcur2.rowcount == 1 : # 表存在已经存在
# 删除 引用table_name的外键
self.DropFKey2_ByName(table_name)
# 删除 引用table_name的View
self.DropView2_ByName(table_name)
try:
self.execute2('DROP TABLE ' + table_name + ' CASCADE')
self.commit2()
#rdb_log.log('DataBase', 'DROP TABLE ' + table_name , 'info')
except:
rdb_log.log('DataBase', 'DROP TABLE ' + table_name + 'failed.' , 'error')
raise
try:
#rdb_log.log('DataBase', 'Now it is creating table ' + table_name +'...', 'info')
if self.execute2(sqlcmd) == -1:
#rdb_log.log('DataBase', 'Create table ' + table_name + 'failed :' + sqlcmd, 'info')
return -1
self.commit2()
#rdb_log.log('DataBase', 'Create table ' + table_name + ' succeed', 'info')
return 0
except:
rdb_log.log('DataBase', 'Create table ' + table_name + 'failed :' + sqlcmd, 'info')
raise
def __init__(self, flag = 1):
self.connected1 = False
self.connected2 = False
if flag != 1: # 源数据库 和 目标数据库相同
self.srv_path1 = self.srv_path2 = common.config.CConfig.instance().getPara('db1')
else: # 源数据库 和 目标数据库可以不相同
self.srv_path1 = common.config.CConfig.instance().getPara('db1')
self.srv_path2 = common.config.CConfig.instance().getPara('db2')
if self.srv_path1 == '' or self.srv_path2 == '':
rdb_log.log('DataBase', "Doesn't exist host IP", 'error')
raise Exception, "host IP doesn't exist."
self.SqlScriprt = common.rdb_sql.SqlScriprt.instance()
self.recordno = 0
def CreateFunction1_ByName(self, function_name):
if self.connected1 != True:
rdb_log.log('DataBase', "Connect1 is not opened.", 'error')
return -1
sqlcmd = self.SqlScriprt.GetSqlScript(function_name)
if sqlcmd == None:
rdb_log.log('DataBase', "Doesn't exist SQL script for function " + function_name, 'error')
return -1
if self.execute1(sqlcmd) == -1:
#rdb_log.log('DataBase', "Create function " + function_name + " Failed.", 'error')
return -1
self.conn1.commit()
#rdb_log.log('DataBase', "Create function " + function_name + " Succeeded.", 'info')
return 0
def GetRoadDisplayClass(self, road_kind):
module_name = 'Road'
i = 0
sheet = self.book.sheet_by_index(0)
ni_code_col = ord('O') - ord('A')
for ni_code in sheet.col(ni_code_col):
if ni_code.value.lower().find(road_kind.lower()) != -1 :
rdb_road_dc_col = ord('X') - ord('A') # Road Display Class
rdb_dc = None
if sheet.cell_value(i, rdb_road_dc_col) != '':
rdb_dc = int(sheet.cell_value(i, rdb_road_dc_col)) # 把十六进制的字串转为十进制数字
else:
rdb_log.log(module_name, "can't find rdb code for NI_Code: " + road_kind, 'warning')
return rdb_dc
i += 1
return None
def GetPath(name):
path = ''
file_object = open('DataBasePath.txt', 'r')
try:
lines = file_object.readlines()
for line in lines:
if line.find(name + '=') == 0:
path = line[len(name + '='):]
break
except:
file_object.close()
rdb_log.log('DataBase', "Doesn't exist file DataBasePath.txt")
exit(1)
finally:
file_object.close()
return path.replace('\n', '')
def IsExistTable(self, table_name):
if self.connected2 != True:
rdb_log.log('DataBase', "Connect2 is not opened.", 'error')
raise
sqlstr = '''
SELECT tablename
FROM pg_tables
where tablename = %s
'''
if self.execute2(sqlstr, (table_name,)) == -1:
#rdb_log.log('DataBase', 'Search table ' + table_name + ' failed :' + sqlstr, 'error')
return False
else:
if self.pgcur2.rowcount == 1 : # 表存在已经存在
return True
return False
def CreateTalbe1(self, sqlcmd, table_name):
'''主要用于创建临时表'''
sqlstr = '''
SELECT tablename
FROM pg_tables
where tablename = %s
'''
if self.execute1(sqlstr, (table_name,)) == -1:
rdb_log.log('DataBase', 'Search table ' + table_name + ' failed :' + sqlstr, 'error')
return -1
else:
if self.pgcur1.rowcount == 1 : # 临时表存在已经存在
try:
self.execute1('DROP TABLE ' + table_name + ' CASCADE')
self.commit1()
#rdb_log.log('DataBase', 'DROP TABLE ' + table_name , 'info')
except:
rdb_log.log('DataBase', 'DROP TABLE ' + table_name + 'failed.' , 'error')
raise
try:
#rdb_log.log('DataBase', 'Now it is creating table ' + table_name +'...', 'info')
if self.execute1(sqlcmd) == -1:
#rdb_log.log('DataBase', 'Create table ' + table_name + 'failed :' + sqlcmd, 'info')
return -1
self.commit1()
#rdb_log.log('DataBase', 'Create table ' + table_name + ' succeed', 'info')
return 0
except:
rdb_log.log('DataBase', 'Create table ' + table_name + 'failed :' + sqlcmd, 'info')
raise
def DropFKey2_ByName(self, table_name):
"删除 引用table_name的所有外键。"
if self.connected2 != True:
rdb_log.log('DataBase', "Connect2 is not opened.", 'error')
return -1
if table_name== None or table_name == '':
rdb_log.log('DataBase', "table_name is None.", 'error')
return -1
# find all fkey, which reference "table_name"
sqlcmd = """
select b.conname as fkey_name, C.relname as table_name
from (
SELECT relid, relname
FROM pg_statio_user_tables
where relname = %s
) AS A
left join pg_constraint as B
ON B.confrelid = A.relid
left join pg_statio_user_tables as C
ON B.conrelid = C.relid
"""
self.execute2(sqlcmd, (table_name,))
row = self.fetchone2()
sqlcmd = ''
while row:
sqlcmd += self.GetDropFKeyStr(row[0], row[1])
row = self.fetchone2()
#print sqlcmd
# Drop all fkey, which reference "table_name"
if sqlcmd != '':
if self.execute2(sqlcmd, table_name) == -1:
return -1
else:
self.commit2()
return 0
def CreateFunction2_ByName(self, function_name):
if self.connected2 != True:
rdb_log.log('DataBase', "Connect2 is not opened.", 'error')
raise
sqlcmd = self.SqlScriprt.GetSqlScript(function_name)
if sqlcmd == None:
rdb_log.log('DataBase', "Doesn't exist SQL script for function " + function_name, 'error')
raise
#
sqlcmd_drop_function = self.__getDropFunctionSqlcmd(sqlcmd)
if sqlcmd_drop_function:
self.execute2(sqlcmd_drop_function)
if self.execute2(sqlcmd) == -1:
#rdb_log.log('DataBase', "Create function " + function_name + " Failed.", 'error')
return -1
self.conn2.commit()
#rdb_log.log('DataBase', "Create function " + function_name + " Succeeded.", 'info')
return 0
def delete_index2(self, index_name):
"""删除索引"""
sqlcmd = """select * from pg_indexes
where indexname = %s
"""
try:
# search the index by name
self.pgcur2.execute(sqlcmd, (index_name,))
if self.pgcur2.rowcount == 1 : # 索引已经存在
try:
self.pgcur2.execute("DROP INDEX " + index_name)
self.conn2.commit()
#rdb_log.log('DataBase', 'DROP index "' + index_name + '"', 'info')
return 0
except:
rdb_log.log('DataBase', 'DROP index ' + index_name + ' failed ' , 'error')
raise
else:
# doesn't exit this index
return 0
except:
rdb_log.log('DataBase', 'DROP index ' + index_name + ' failed :' + sqlcmd, 'error')
raise
def DropView2_ByName(self, table_name):
"删除 引用table_name的所有View。"
if self.connected2 != True:
rdb_log.log('DataBase', "Connect2 is not opened.", 'error')
return -1
if table_name == None or table_name == '':
rdb_log.log('DataBase', "table_name is None.", 'error')
return -1
# find all views, which reference "table_name"
sqlcmd = """
SELECT viewname
FROM pg_views
where definition like %s or definition like %s;
"""
#sqlcmd += ",%' or definition like '%" +table_name+ " %'"
#print sqlcmd
if self.execute2(sqlcmd, ("%"+table_name +" %", "%"+table_name +",%")) == -1:
return -1
row = self.fetchone2()
sqlcmd = ''
while row:
sqlcmd += self.GetDropViewStr(row[0])
row = self.fetchone2()
#print sqlcmd
# Drop all views, which reference "table_name"
if sqlcmd != '':
if self.execute2(sqlcmd, table_name) == -1:
return -1
else:
self.commit2()
return 0
def GetStratify(self, kind, arrt):
"""取得分层信息
Kind: 要转换的种别 1: link, 2: link名字， 3: 背景， 4： 背景字， 5： POI文字, 6:背景线 7:背景面 8: 水系
arrt: 相关属性 1: link等级和属性 2: link等级和属性 3: 背景属性, 4: 背景字代码 5: POI代码 6:背景种别 7:背景种别 8: 水系种别
return(返回值):
code: 种别
nametype: 类别 0:RESERVED；1: 重心文字；2: 线状名称；3：记号+文字列
prior: 优先级
low_level: 数值越大,level等级越低; 19---0
high_level:同上
"""
module_name = ''
if kind == 2: # 求link名字分层
module_name = 'Link Name'
elif kind == 5: # POI文字
module_name = 'POI Name'
elif kind == 6: # 背景线
module_name = 'Line'
elif kind == 7: # 背景面
module_name = 'Polygon'
elif kind == 8: # 水系
module_name = 'water'
else:
rdb_log.log('Stratify', "error kind" + repr(kind), 'warning')
return None, None, None, None, None
i = 0
sheet = self.book.sheet_by_index(0)
ni_code_col = ord('O') - ord('A')
for ni_code in sheet.col(ni_code_col):
if ni_code.value.lower().find(arrt.lower()) != -1 :
rdb_code_col = ord('D') - ord('A') # 种别Code列号
rdb_prio_col = ord('E') - ord('A') # 名称表示優先度列号
rdb_type_col = ord('F') - ord('A') # 文字列Type列号
rdb_low_col = ord('V') - ord('A') # 最低层列号
rdb_high_col = ord('W') - ord('A') # 最高层列号
rdb_code, name_prio,name_type, low_col, high_col = None, None, None, None, None
if sheet.cell_value(i, rdb_code_col) != '':
rdb_code = int(sheet.cell_value(i, rdb_code_col)[0:6], 16) # 把十六进制的字串转为十进制数字
else:
rdb_log.log(module_name, "can't find rdb code for NI_Code " + arrt, 'warning')
if sheet.cell_value(i, rdb_prio_col) != '':
name_prio = sheet.cell_value(i, rdb_prio_col)
else :
if module_name != 'Line' and module_name != 'Polygon' and module_name != 'water':
rdb_log.log(module_name, "can't find Priority code for NI_Code: " + arrt, 'warning')
if sheet.cell_value(i, rdb_type_col) != '':
name_type = sheet.cell_value(i, rdb_type_col)
else :
if module_name != 'Line' and module_name != 'Polygon' and module_name != 'water':
rdb_log.log(module_name, " can't find type code for NI_Code: " + arrt, 'warning')
if sheet.cell_value(i, rdb_low_col) != '':
low_col = sheet.cell_value(i, rdb_low_col)
else:
if module_name != 'Line' and module_name != 'Polygon' and module_name != 'water':
rdb_log.log(module_name, "can't find low level code for NI_Code: " + arrt, 'warning')
if sheet.cell_value(i, rdb_high_col) != '':
high_col = sheet.cell_value(i, rdb_high_col)
else:
if module_name != 'Line' and module_name != 'Polygon' and module_name != 'water':
rdb_log.log(module_name, "can't find high level code for NI_Code: " + arrt, 'warning')
return rdb_code, name_type, name_prio, low_col, high_col
i += 1
rdb_log.log(module_name, "can't find the information for NI_Code: " + arrt, 'warning')
return None, None, None, None, None
def CreateIndex2_ByName(self, index_name):
sqlcmd = self.SqlScriprt.GetSqlScript(index_name)
if sqlcmd == None:
rdb_log.log('DataBase', "Doesn't exist SQL script for index " + index_name, 'error')
raise
return self.create_index2(sqlcmd, index_name)