def execSQLScript(sql):
logger.info(" Executing SQL commands")
connection = psycopg2.connect(host=config.databaseHost,
database=config.databaseName,
port=config.databasePort,
user=config.databaseUserName,
password=config.databasePassword)
cursor = connection.cursor()
try:
if (True):
cursor.execute(sql)
connection.commit()
else:
sqlItems = sql.split(";")
for sqlItem in sqlItems:
cursor.execute(sqlItem)
connection.commit()
except psycopg2.Error as e:
logPsycopg2Error(e)
finally:
cursor.close()
connection.close()
logger.info(" Executing SQL commands - done.")
pass
def renameTempTable(connection):
logger.info("Renaming table _ac_gids to ac_gids.")
cursor = connection.cursor()
cursor.execute(
"drop table if exists ac_gids;alter table _ac_gids rename to ac_gids;")
cursor.close()
logger.info("Done.")
def _getRUIANVersionDate():
result = "unassigned _getRUIANVersionDate"
try:
connection = psycopg2.connect(host=DATABASE_HOST,
database=DATABASE_NAME,
port=PORT,
user=USER_NAME,
password=PASSWORD)
cursor = connection.cursor()
try:
query = 'select * from ruian_dates'
cursor.execute(query)
row = cursor.fetchone()
result = row[1]
except psycopg2.Error as e:
result = "Error: Could not execute query to %s at %s:%s as %s:%s" % (
DATABASE_NAME, DATABASE_HOST, PORT, USER_NAME, query)
logger.info("Error: " + e.pgerror)
return "ERROR:" + e.pgerror
finally:
cursor.close()
connection.close()
except psycopg2.Error as e:
result = "Error: Could connect to %s at %s:%s as %s\n%s" % (
DATABASE_NAME, DATABASE_HOST, PORT, USER_NAME, str(e))
logger.info(result)
return result
def createTempTable(connection):
logger.info("Creating table ac_gids")
cursor = connection.cursor()
try:
cursor.execute("drop table if exists ac_gids;")
cursor.execute(
"CREATE TABLE ac_gids (gid integer NOT NULL, address text);")
logger.info("Done.")
finally:
cursor.close()
def getAddressRows(connection):
logger.info("Retrieving address rows")
cursor = connection.cursor()
try:
query = 'select nazev_ulice, cast(cislo_domovni as text), nazev_obce, cast(psc as text), cast(cislo_orientacni as text), znak_cisla_orientacniho, nazev_casti_obce, typ_so, nazev_mop, gid from address_points '
cursor.execute(query)
logger.info("Done.")
return cursor
except:
logger.error("Error:Selecting address rows failed.")
exitApp()
def __init__(self):
try:
self.conection = psycopg2.connect(host=DATABASE_HOST,
database=DATABASE_NAME,
port=PORT,
user=USER_NAME,
password=PASSWORD)
except psycopg2.Error as e:
logger.info(
"Error: Could not connect to database %s at %s:%s as %s\n%s" %
(DATABASE_NAME, DATABASE_HOST, PORT, USER_NAME, str(e)))
self.conection = None
def execSQLScriptFile(sqlFileName, msg, exitIfFileNotFound=True):
logger.info(msg)
sqlFileName = getRUIANServicesSQLScriptsPath() + sqlFileName
logger.info(" Loading SQL commands from %s" % sqlFileName)
if not os.path.exists(sqlFileName):
if exitIfFileNotFound:
logger.error("ERROR: File %s not found." % sqlFileName)
exitApp()
else:
logger.warning("ERROR: File %s not found." % sqlFileName)
return
inFile = codecs.open(sqlFileName, "r", "utf-8")
sql = inFile.read()
inFile.close()
logger.info(" Loading SQL commands - done.")
execSQLScript(sql)
def buildGIDsTable():
logger.info("Building table ac_gids")
logger.info("------------------------")
connection = psycopg2.connect(host=config.databaseHost,
database=config.databaseName,
port=config.databasePort,
user=config.databaseUserName,
password=config.databasePassword)
try:
createTempTable(connection)
cursor = getAddressRows(connection)
try:
if cursor == None: return
logger.info("Inserting rows")
logger.info("----------------------")
insertCursor = connection.cursor()
builder = HTTPShared.MimeBuilder("texttoonerow")
row_count = 0
gaugecount = 0
for row in cursor:
row_count += 1
gaugecount += 1
try:
street, houseNumber, locality, zipCode, orientationNumber, orientationNumberCharacter, localityPart, typ_so, nazev_mop, gid = row
houseNumber, recordNumber = HTTPShared.analyseRow(
typ_so, houseNumber)
districtNumber = HTTPShared.extractDictrictNumber(
nazev_mop)
rowLabel = compileaddress.compileAddress(
builder, street, houseNumber, recordNumber,
orientationNumber, orientationNumberCharacter, zipCode,
locality, localityPart, districtNumber)
insertSQL = "INSERT INTO ac_gids (gid, address) VALUES (%s, '%s')" % (
gid, rowLabel)
insertCursor.execute(insertSQL)
connection.commit()
if gaugecount >= 1000:
gaugecount = 0
logger.info(str(row_count) + " rows")
except psycopg2.Error as e:
logPsycopg2Error(e)
logger.error(
str(row_count) + " " + insertSQL + " failed. ")
exitApp()
pass
logger.info("Done - %d rows inserted." % row_count)
finally:
cursor.close()
#renameTempTable(connection)
logger.info("Building table ac_gids done.")
finally:
connection.close()
pass
def __init__(self):
try:
self.conection = psycopg2.connect(host = DATABASE_HOST, database = DATABASE_NAME, port = PORT, user = USER_NAME, password = PASSWORD)
except psycopg2.Error as e:
logger.info("Error: Could not connect to database %s at %s:%s as %s\n%s" % (DATABASE_NAME, DATABASE_HOST, PORT, USER_NAME, str(e)))
self.conection = None