def insertIntoDataElements(self,
tableName,
whereClause,
ctaDefinition=None):
delPos = tableName.rfind("_")
symbolType = tableName[delPos + 1:delPos + 2].upper()
if whereClause:
whereClause = " where " + whereClause
if ctaDefinition:
ctaPrefix, ctaTables = ctaDefinition
ctaTables = ", " + ctaTables + " "
else:
ctaPrefix = ""
ctaTables = ""
sql = setParameters(
"""with cta as ()
insert into data.elements (source_id, source_elt_id, target_elt_id, source_geom, target_geom)
select 'z_plocharuzna_b::' || objectid::text as source_id,
'B' || znacka::text as source_elt_id,
'B' || znacka::text as target_elt_id,
wkb_geometry as source_geom,
null as target_geom
from public.z_plocharuzna_b, cta_tables where znacka = 4202700
""", {
", cta_tables": ctaTables,
"with cta as ()": ctaPrefix,
"z_plocharuzna_b": tableName,
"'B'": "'%s'" % symbolType,
"where znacka = 4202700": whereClause
})
self.execute(sql)
def convertCode(code, lexer="python", removeFirstDiv=True):
import sys, os
import pygments.cmdline
from pygeotoolbox.sharedtools import saveStrToFile, fileRead
workingDir = os.path.dirname(__file__)
codeFileName = workingDir + "/~temp.txt"
htmlFileName = workingDir + "/~temp.html"
saveStrToFile(code, codeFileName)
args = []
args.extend(sys.argv)
args.extend(['-l', lexer, '-o', htmlFileName, codeFileName])
pygments.cmdline.main(args)
result = fileRead(htmlFileName)
result = setParameters(result, {
'"': "'",
"\n": "<br>",
"\t": " "
})
os.remove(codeFileName)
os.remove(htmlFileName)
if removeFirstDiv:
result = result[result.find(">") + 1:]
result = result[:result.rfind("</pre>")]
return result
def execute(self, sql, parameters = {}):
"""Executes sql command. Before execution, it replaces parameters if provided.
:param str sql: Command sequence to be executed;
:param dict parameters: Parameters to be used in a query.
"""
assert isinstance(sql, basestring)
assert isinstance(parameters, dict)
if self.muted or not sql:
return
sql = setParameters(sql, parameters)
connection = self.connection
cursor = connection.cursor()
#cursor = self.connection.cursor()
try:
startTime = time.time()
cursor.execute(sql)
self.executeElapsedTime += time.time() - startTime
connection.commit()
#self.connection.commit()
except psycopg2.Error as e:
log.error(str(e) + "\n" + sql)
finally:
cursor.close()
def executeSelectSQL(self, sql, parameters={}):
sql = setParameters(sql, sqlkeywords.sqlKeywordsValues)
sql = setParameters(sql, parameters)
if True: # @TODO Hook fro error 'current transaction is aborted, commands ignored until end of transaction block'
cursor = self.connection.cursor()
else:
c = psycopg2.connect(self.connectionParams)
cursor = c.cursor()
try:
startTime = time.time()
cursor.execute(sql)
self.selectElapsedTime += time.time() - startTime
return cursor
except Exception as inst:
log.logger.error("PostGISConnector.executeSelectSQL:'%s' '%s'" %
(sql, str(inst)))
return None
def execute(self, sql, values={}):
if self.__mute:
return
sql = setParameters(sql, sqlkeywords.sqlKeywordsValues)
sql = setParameters(sql, values)
cursor = self.connection.cursor()
try:
startTime = time.time()
cursor.execute(sql)
self.executeElapsedTime += time.time() - startTime
self.connection.commit()
except Exception as inst:
log.logger.error("PostGISConnector.execute():" + str(inst) +
" at " + sql)
finally:
cursor.close()
def dumpdict(representations, geomFieldNames, visibilityFieldNames):
result = []
for representation in representations:
representationItem = {
Config.CAPTION_KEY: representation.caption,
Config.SOURCE_FEATURES_KEY: [],
Config.TARGET_FEATURES_KEY: []
}
result.append(representationItem)
items = representationItem[Config.SOURCE_FEATURES_KEY]
for tableName, ids in representation.context.iteritems():
sql = "select objectid, znacka, st_astext((st_dump(wkb_geometry)).geom) from public.z_plocharuzna_p where objectid in (93473)"
params = {
"(93473)": listToSqlStr(ids),
"z_plocharuzna_p": tableName
}
rows = database.executeSelectSQL(sql, params)
dumpItems(tableName, rows, items)
items = representationItem[Config.TARGET_FEATURES_KEY]
if representation.solution == {}:
solutionItems = representation.context
else:
solutionItems = representation.solution
for tableName, ids in solutionItems.iteritems():
sql = """
select objectid, znacka, st_astext((st_dump(wkb_geometry)).geom), viditelnost10 from public.z_plocharuzna_p where objectid in (93473) and (viditelnost10=0 or (viditelnost10=1 and zm10_geom is null))
union all
select objectid, znacka, st_astext((st_dump(zm10_geom)).geom), viditelnost10 from public.z_plocharuzna_p where objectid in (93473) and viditelnost10 = 1 and zm10_geom is not null
"""
#sql = "select objectid, znacka, st_astext((st_dump(wkb_geometry)).geom) from public.z_plocharuzna_p where objectid in (93473)"
sqlParams = {
"(93473)": listToSqlStr(ids),
"z_plocharuzna_p": tableName,
"zm10_geom": geomFieldNames[str(representation.scale)],
"viditelnost10": visibilityFieldNames[str(representation.scale)]
}
rows = database.executeSelectSQL(sql, sqlParams)
dumpedItemsCount = dumpItems(tableName, rows, items)
if dumpedItemsCount <> len(ids):
from pygeotoolbox.sharedtools import setParameters
sql = setParameters(sql, sqlParams)
print sql
print __file__, "dumpdict() Error", dumpedItemsCount, "<>", len(ids), "representation", representation.caption
return result
def executeBuffer(self, sql, buffer, limit=1000, parameters=None):
"""Inserts sql into buffer, then executes it, if number of commands in it is higher than limit or does nothing. returns either empty or original buffer.
:param str sql:sql command to be inserted to buffer
:param list of str buffer: buffer of SQL commands
:param int limit: maximum number of items in a buffer before being executed
:return list of str: empty list or buffer
"""
if not sql.endswith(";"):
sql += ";"
if parameters:
sql = setParameters(sql, parameters)
buffer.append(sql)
if len(buffer) > limit:
buffer = self.clearBuffer(buffer)
return buffer
def executeSelectSQL(self, sql, parameters = {}, iterSize=0):
"""Runs SQL query and returns database cursor with query result.
:param str sql: Query to be executed.
:param dict parameters: Parameters to be used in a query.
:return cursor : Cursor with result or None if fails.
"""
assert isinstance(sql, basestring)
assert isinstance(parameters, dict)
assert isinstance(iterSize, int)
sql = setParameters(sql, parameters)
cursor = self.connection.cursor()
if iterSize:
cursor.itersize = iterSize
startTime = time.time()
cursor.execute(sql)
self.selectElapsedTime += time.time() - startTime
return cursor
def executeSQL(self, sql, params={}):
sql = setParameters(sql, params)
self.executeBuffer = database.executeBuffer(sql, self.executeBuffer)