def calculateinterdependencies(geom): createinterdependency(geom) import os, subprocess from Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() # Choose your PostgreSQL version here # os.environ['PATH'] += r';C:\Program Files\PostgreSQL\9.5\bin' # D:\usbgis\apps\postgresql93\bin os.environ['PATH'] += r';D:\usbgis\apps\postgresql93\bin' # http://www.postgresql.org/docs/current/static/libpq-envars.html os.environ['PGHOST'] = 'localhost' os.environ['PGPORT'] = '5432' os.environ['PGUSER'] = '******' os.environ['PGPASSWORD'] = '******' os.environ['PGDATABASE'] = 'roads' cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < D:/Data/Python/create_interdependencies.txt" print cmd subprocess.call(cmd, shell=True)
def deleteValutazione(databasename): import os, subprocess from Connection import Connection con2 = Connection() con = con2.getConnection() sql = "delete from valutazione " #maquina = " -h 127.0.0.1 -p 5434" maquina = " -h " + con.host + " -p " + con.port from Utils import Directory2 dir = Directory2.getPathScripts() # file = "C:/Data/Python/sql_temporal.txt" file = dir + "sql_temporal.txt" Writes.writefile(file, sql) logging.debug("sql") logging.debug(sql) cmd = "psql -U " + con.user + maquina + " " + con.database + " < " + file print cmd subprocess.call(cmd, shell=True)
def importValutazioneFromText(filename, databasename): import os, subprocess from Connection import Connection #maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() sql = "Copy valutazione from '" + filename + "' delimiter ','" print "\n" print sql from Utils import Directory2 dir = Directory2.getPathScripts() # file = "C:/Data/Python/sql_temporal.txt" file = dir + "sql_temporal.txt" Writes.writefile(file, sql) logging.debug("sql") logging.debug(sql) maquina = " -h " + con.host + " -p " + con.port cmd = "psql -U " + con.user + maquina + " " + con.database + " < " + file print cmd subprocess.call(cmd, shell=True)
class PageManager: __connection = Connection(Settings.sqllite_file_location) @staticmethod def get_by_id(id): result = PageManager.__connection.execute('SELECT id, url_id, html, is_parsed FROM Page WHERE id = ?', (id,)) return Models.Page.Page(tuple=result[0]) if result else None @staticmethod def get_by_url_id(url_id): result = PageManager.__connection.execute( 'SELECT id, url_id, html, is_parsed FROM Page WHERE url_id = ?', (url_id,) ) return Models.Page.Page(tuple=result[0]) if result else None @staticmethod def save(page_object): if PageManager.get_by_url_id(page_object.url.id): PageManager.__connection.execute( 'UPDATE Page SET url_id = ?, html = ?, is_parsed = ? WHERE url_id = ?', (page_object.url.id, page_object.html, page_object.is_parsed, page_object.url.id) ) else: PageManager.__connection.execute( 'INSERT INTO Page(url_id, html, is_parsed) VALUES (?, ?, ?)', (page_object.url.id, page_object.html, page_object.is_parsed) ) return PageManager.get_by_url_id(page_object.url.id) @staticmethod def get_pending_page(): result = PageManager.__connection.execute('SELECT id, url_id, html, is_parsed FROM Page WHERE is_parsed = 0 LIMIT 1') return Models.Page.Page(tuple=result[0]) if result else None
def executeSQL(sqllist): import os, subprocess from Database.Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() # Choose your PostgreSQL version here # os.environ['PATH'] += r';C:\Program Files\PostgreSQL\9.5\bin' # D:\usbgis\apps\postgresql93\bin os.environ['PATH'] += r';C:/usbgis/apps/postgresql93/bin' # http://www.postgresql.org/docs/current/static/libpq-envars.html os.environ['PGHOST'] = 'localhost' os.environ['PGPORT'] = '5432' os.environ['PGUSER'] = '******' os.environ['PGPASSWORD'] = '******' os.environ['PGDATABASE'] = 'roads' import Writes s = "" for x in sqllist: s = s + x + ";\n" logging.debug(" \n\n\n") logging.debug(" SSSSSSSSSSS") logging.debug(s) logging.debug(" \n\n\n") from Utils import Directory dir = Directory.getPathTempDir() file = dir + "sqltemporal2.txt" file_temp = dir + "errores.txt" Writes.writefile(file, s) logging.debug(" Coondection " + con.database) cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < " + file + " > " + file_temp + "" print cmd subprocess.call(cmd, shell=True)
def calculatenumberofstructuresinfragilityview(databasename): valor = -1 try: import logging from Database.Connection import Connection logging.debug(" calculating number of structures in database: " + databasename) import psycopg2 import numpy con2 = Connection() strcon = con2.getStringToConnect(databasename) logging.debug(" string de connection") print "strcon" print strcon logging.debug(strcon) conn = psycopg2.connect(strcon) cur = conn.cursor() try: cur.execute( "SELECT count(gid) from view_fragility_curve_structure") #cur.execute("""SELECT gid, (7* vulfactor - 13.1)/2.3 from popoliforpostgres""") sqllist = [] rows = cur.fetchall() print "\nRows: \n" for row in rows: try: print "Row 1 " + str(row[0]) if str(row[0]) == 'None': valor = 0 else: valor = row[0] except: valor = -1 except: print "I can't SELECT from view_fragility_curve_structure" except: import sys print "I am unable to connect to the database.", sys.exc_info()[0] raise return valor
class UrlDownloadManager: __connection = Connection(Settings.sqllite_file_location) @staticmethod def get_by_id(id): result = UrlDownloadManager.__connection.execute( 'SELECT id, url, is_downloaded FROM UrlDownload WHERE id = ?', (id, )) return Models.UrlDownload.UrlDownload( tuple=result[0]) if result else None @staticmethod def get_by_url(url): result = UrlDownloadManager.__connection.execute( 'SELECT id, url, is_downloaded FROM UrlDownload WHERE url LIKE ?', (url, )) return Models.UrlDownload.UrlDownload( tuple=result[0]) if result else None @staticmethod def exists(url): result = UrlDownloadManager.__connection.execute( 'SELECT id FROM UrlDownload WHERE url like ?', (url, )) return result is not None @staticmethod def save(url_download_object): database_url = UrlDownloadManager.get_by_url(url_download_object.url) if database_url is not None: UrlDownloadManager.__connection.execute( 'UPDATE UrlDownload SET url = ?, is_downloaded = ? WHERE id = ?', (url_download_object.url, url_download_object.is_downloaded, database_url.id)) else: UrlDownloadManager.__connection.execute( 'INSERT INTO UrlDownload(url, is_downloaded) VALUES (?, ?)', (url_download_object.url, False)) return UrlDownloadManager.get_by_url(url_download_object.url) @staticmethod def get_pending_download_urls(): results = UrlDownloadManager.__connection.execute( 'SELECT id, url, is_downloaded FROM UrlDownload WHERE is_downloaded = 0' ) download_urls = [] if results: for result in results: download_url = Models.UrlDownload.UrlDownload(tuple=result) download_urls.append(download_url) return download_urls if download_urls else None
class UrlManager: __connection = Connection(Settings.sqllite_file_location) @staticmethod def get_by_id(id): result = UrlManager.__connection.execute( 'SELECT id, url FROM Url WHERE id = ?', (id, )) return Models.Url.Url(tuple=result[0]) if result else None @staticmethod def get_by_url(url): result = UrlManager.__connection.execute( 'SELECT id, url FROM Url WHERE url LIKE ?', (url, )) return Models.Url.Url(tuple=result[0]) if result else None @staticmethod def exists(url): result = UrlManager.__connection.execute( 'SELECT id FROM Url WHERE url like ?', (url, )) return result is not None @staticmethod def save(url_object): database_url = UrlManager.get_by_url(url_object.url) if database_url is not None: UrlManager.__connection.execute( 'UPDATE Url SET url = ? WHERE id = ?', (url_object.url, database_url.id)) else: UrlManager.__connection.execute('INSERT INTO Url(url) VALUES (?)', (url_object.url, )) return UrlManager.get_by_url(url_object.url) @staticmethod def get_pending_urls(): results = UrlManager.__connection.execute( 'SELECT id, url FROM Url WHERE id NOT in (SELECT url_id FROM Page)' ) urls = [] if results: for result in results: url = Models.Url.Url(tuple=result) urls.append(url) return urls if urls else None
def removeALLLayers(): import os, subprocess from Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() sql = "drop database prueba7" file = "C:/Data/Python/sql_temporal.txt" writefile(file, sql) databasename = "template1" cmd = "psql -U " + con.user + maquina + " " + databasename + " < " + file + " > 'C:/Data/Python/errores.txt'" subprocess.call(cmd, shell=True)
def exportFragilityValuesToExcel(filename, filename2, databasename): import os, subprocess from Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() sql = "Copy (Select idfragility, level, param1, param2 From fragility_curve_level order by idfragility, level) To '" + filename + "' With CSV DELIMITER ',' HEADER;" print "connectdb 351" print sql from Utils import Directory #file = "C:/Data/Python/sql_temporal.txt" dir = Directory.getPathTempDir() file = dir + "sql_teemporal.txt" writefile(file, sql) cmd = "psql -U " + con.user + maquina + " " + databasename + " < " + file print cmd subprocess.call(cmd, shell=True) sql = "Copy (Select * from fragility_curve_structure order by gid) To '" + filename2 + "' With CSV DELIMITER ',' HEADER;" #file = "C:/Data/Python/sql_temporal.txt" print "connectdb 367" print sql writefile(file, sql) cmd = "psql -U " + con.user + maquina + " " + databasename + " < " + file print cmd subprocess.call(cmd, shell=True)
#/usr/bin/python2. # # import string import sys from Database.Connection import Connection #maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() # Try to connect ''' try: conn=psycopg2.connect("dbname='prueba4' userpostgres' password='******'") except: print "I am unable to connect to the database." cur = conn.cursor() try: cur.execute("""SELECT * from popoliforpostgres""") except: print "I can't SELECT from popoli for postgres" rows = cur.fetchall() print "\nRows: \n" for row in rows: print " ", row[1]
def populateEpicenter(momemtum, x, y, texto): ''' try: parametros = texto.split(",") a = parametros[0] b = parametros[1] c = parametros[2] d = parametros[3] a = a.replace("a=", "") b = b.replace("b=", "") c = c.replace("c=", "") d = d.replace("d=", "") otrosql = "update hazard set a="+a +", b=" +b +", c=" +c +", d=" + d+"" otrosql = otrosql.replace("\n", "") except : import sys print "parse error:", sys.exc_info()[0] otrosql = "" if (otrosql==""): otrosql = "update hazard set a=random(), b=random() , c=random(), d=random()" print " Populate epicenter " ''' # sqllist=[otrosql] sqllist = [] sql = "insert into epicenter values ('0', 'epicenter', ST_SetSRID(ST_MakePoint(" + str( x) + "," + str(y) + "), 4326))" sqllist.append(sql) sqllist.append("update hazard set momentum = " + momemtum) print sql executeSQL(sqllist) # run the next three sqls by console ''' these instructions are runned by console otrosql = "update distance_from_epicenter set geom = (select ST_MakeLine((select geom from hazard where hazard.gid=distance_from_epicenter.gid), (select geom from epicenter where gid='0')))" sql2 = "update distance_from_epicenter set distance=ST_Length(geom)" sql3 = "update hazard set rdistance=ST_Length((select geom from distance_from_epicenter where distance_from_epicenter.gid=hazard.gid))/1000" ''' # sqllist.append(otrosql) # sqllist.append(sql2) # sqllist.append(sql3) # aqui import os, subprocess from Database.Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() # Choose your PostgreSQL version here # os.environ['PATH'] += r';C:\Program Files\PostgreSQL\9.5\bin' # D:\usbgis\apps\postgresql93\bin os.environ['PATH'] += r';D:\usbgis\apps\postgresql93\bin' # http://www.postgresql.org/docs/current/static/libpq-envars.html os.environ['PGHOST'] = 'localhost' os.environ['PGPORT'] = '5432' os.environ['PGUSER'] = '******' os.environ['PGPASSWORD'] = '******' os.environ['PGDATABASE'] = 'roads' cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < C:/Data/Python/copy_from_cvs.txt" print cmd subprocess.call(cmd, shell=True)
def createEpicenter(momentum, x, y, parametros, objetos, modelohazard): import os, subprocess from Database.Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() # Choose your PostgreSQL version here # os.environ['PATH'] += r';C:\Program Files\PostgreSQL\9.5\bin' # D:\usbgis\apps\postgresql93\bin db = con2.getPathDatabase() # os.environ['PATH'] += r';D:\usbgis\apps\postgresql93\bin' # path_database=db["path_database"] path_database = con2.getPathDatabase() os.environ['PATH'] += "r" + path_database # http://www.postgresql.org/docs/current/static/libpq-envars.html ''' os.environ['PGHOST'] = 'localhost' os.environ['PGPORT'] = '5432' os.environ['PGUSER'] = '******' os.environ['PGPASSWORD'] = '******' os.environ['PGDATABASE'] = 'prueba4' ''' from Utils import Directory sqld = Directory.getPathSqlDir() sql_temp = Directory.getPathTempDir() print " log path scripts 3502 " print sqld #cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < " + sqld + "random_points_in_polygons.txt > " + sql_temp + "errores2.txt" #subprocess.call(cmd, shell=True) cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < " + sqld + "Hazard/create_epicenter_hazard1.txt > " + sql_temp + "errores2.txt" if (objetos == 1): cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < " + sqld + "Hazard/create_epicenter_hazard.txt > " + sql_temp + "errores1.txt" print cmd subprocess.call(cmd, shell=True) populateEpicenter(momentum, x, y, parametros) cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < " + sqld + "Hazard/update_epicenter.txt > " + sql_temp + "errores3.txt" print cmd subprocess.call(cmd, shell=True) if modelohazard == 0: cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < " + sqld + "Hazard/update_hazard1.txt > " + sql_temp + "errores4.txt" print cmd else: cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < " + sqld + "Hazard/update_hazard2.txt > " + sql_temp + "errores5.txt" print cmd subprocess.call(cmd, shell=True)
def updateHazardParameters(momentum, texto): parametros = texto.split(",") sqlhazard = "" try: a = parametros[0] b = parametros[1] c = parametros[2] d = parametros[3] a = a.replace("a=", "") b = b.replace("b=", "") c = c.replace("c=", "") d = d.replace("d=", "") sql = "update hazard set a=" + a + ",b=" + b + ",c=" + c + ",d=" + d + "" sql = sql.replace("\n", "") sqlhazard = sql except: print "parse error:", sys.exc_info()[0] sql = "" print sql if sql != "": sqllist0 = ["update hazard set momentum=" + momentum] #executeSQL(sqllist0) sqllist = [sql] sqllist.append( "update hazard set hazard=a + b*momentum + c*rdistance + d*soilfactor" ) #executeSQL(sqllist) #"update hazard set momentum = get_random_number(0, 9), soilfactor = get_random_number(0, 1)" #"update hazard set rdistance = (select distance from distance_from_epicenter where distance_from_epicenter.gid = hazard.gid) / 1000" out_file = open("C:\Data\qgis\updatehazard.txt", "w") out_file.write(sqlhazard + ";\n") out_file.write("update hazard set momentum=" + momentum + ";\n") out_file.write( "update hazard set soilfactor = get_random_number(0, 1);\n") out_file.write( "update hazard set rdistance = (select distance from distance_from_epicenter where distance_from_epicenter.gid = hazard.gid) / 1000;\n" ) out_file.write( "update hazard set hazard=a + b*momentum + c*rdistance + d*soilfactor;\n" ) out_file.close() import os, subprocess from Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() # Choose your PostgreSQL version here # os.environ['PATH'] += r';C:\Program Files\PostgreSQL\9.5\bin' # D:\usbgis\apps\postgresql93\bin os.environ['PATH'] += r';D:\usbgis\apps\postgresql93\bin' # http://www.postgresql.org/docs/current/static/libpq-envars.html os.environ['PGHOST'] = 'localhost' os.environ['PGPORT'] = '5432' os.environ['PGUSER'] = '******' os.environ['PGPASSWORD'] = '******' os.environ['PGDATABASE'] = 'prueba7' #cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < C:/Data/Python/copy_from_cvs.txt" cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < C:\Data\Python\update_epicenter.txt > C:\Data\Python\errores.txt" print cmd subprocess.call(cmd, shell=True) cmd = "psql -d " + con.database + " -U " + con.user + maquina + " < C:\Data\qgis\updatehazard.txt > C:\Data\Python\errores.txt" print cmd subprocess.call(cmd, shell=True)
def checkdatabase(databasename): valor = 1 try: # logging.debug(" calculating number of structures in database: " + databasename) import psycopg2 import numpy con2 = Connection() con = con2.getConnection() #strcon = con2.getStringToConnect(con.database) name_data0base = "test" strcon = con2.getStringToConnect(databasename) logging.debug(" string de connection") print "strcon" print strcon logging.debug(strcon) try: conn = psycopg2.connect(strcon) ''' cur = conn.cursor() cur.execute("SELECT count(gid) from p+") # cur.execute("""SELECT gid, (7* vulfactor - 13.1)/2.3 from popoliforpostgres""") sqllist = [] rows = cur.fetchall() print "\nRows: \n" for row in rows: try: print "Row 1 " + str(row[0]) if str(row[0]) == 'None': valor = 0 else: valor = row[0] except: valor = -1 ''' except: print "I can't SELECT from popoli for postgres" valor = -1 except: import sys print "I am unable to connect to the database.", sys.exc_info()[0] raise valor = -1 return valor
def updateValutazioneLot(filrname, databasename): import os, subprocess from Connection import Connection maquina = " -h 127.0.0.1 -p 5434" con2 = Connection() con = con2.getConnection() sql = "delete from valutazione;" sql += "copy valutazione(idstructure, idparam, x, z, f, w1, w2,n) FROM 'C:/Data/datos_valutazione.csv' DELIMITER ';' CSV" file = "C:/Data/Python/sql_temporal.txt" writefile(file, sql) cmd = "psql -U " + con.user + maquina + " " + databasename + " < " + file print cmd subprocess.call(cmd, shell=True) sql = "update valutazione set paramvalue='1.5' where (idparam=1 or idparam=2 or idparam=3 or idparam=13 );" print sql sql += "update valutazione set paramvalue='1' where (idparam=4 or idparam=5 or idparam=6 or idparam=7 );" print sql sql += "update valutazione set paramvalue='0.5' where idparam=9;" print sql sql += "update valutazione set paramvalue='1' where idparam=10;" print sql + ";" sql += "update valutazione set paramvalue='0.8' where (idparam=11 or idparam=8);" print sql + ";" sql += "update valutazione set paramvalue='0.5' where idparam=12;" print sql + ";" sql += "update valutazione set paramvalue='0.3' where idparam=14;" print sql + ";" ''' vki = w1 * z * f vkp = w2 * z * n ''' sql += "update valutazione set vkimportance=w1*z*f;" print sql + ";" sql += "update valutazione set vkprotection=w2*z*n;" print sql + ";" writefile(file, sql) cmd = "psql -U " + con.user + maquina + " " + databasename + " < " + file + " > C:/Data/Python/errores.txt " print cmd subprocess.call(cmd, shell=True) print sql + ";" # cur.execute(otrosql) sql = "SELECT UpdateLayerStatistics(\'popoliforpostgres\')" writefile(file, sql) cmd = "psql -U " + con.user + maquina + " " + databasename + " < " + file + " > C:/Data/Python/errores2.txt " print cmd subprocess.call(cmd, shell=True)
def writeSQLtoFile(sqllist, filename): ##conn = db.connect('C:/Data/Popoli/popolispatial.sqlite') import psycopg2 import xlwt wb = xlwt.Workbook() ws = wb.add_sheet('Valutations indicatores') rowNum = 0 #colNum = 0 ws.write(rowNum, 0, 'idstructure') ws.write(rowNum, 1, 'idparam') ws.write(rowNum, 2, 'x') ws.write(rowNum, 4, 'z') ws.write(rowNum, 5, 'f') ws.write(rowNum, 6, 'w1') ws.write(rowNum, 7, 'w2') ws.write(rowNum, 8, 'n') rowNum = 1 con = Connection() con2 = con.getConnection() database = con2.database print " database is " + database conn = psycopg2.connect(con2.getStringToConnect(database)) # creating a Cursor cur = conn.cursor() for sql in sqllist: # execute a simple query # query = db.exec_(str) # cur.execute(otrosql) cur.execute(sql) print " sql iside write " print sql try: rows = cur.fetchall() print('Total Row(s):', cur.rowcount) for row in rows: #print(row) ws.write(rowNum, 0, row[0]) # row, column, value ws.write(rowNum, 1, row[1]) ws.write(rowNum, 2, row[2]) ws.write(rowNum, 3, row[3]) ws.write(rowNum, 4, row[4]) ws.write(rowNum, 5, row[5]) ws.write(rowNum, 6, row[6]) ws.write(rowNum, 7, row[7]) ws.write(rowNum, 8, row[8]) rowNum = rowNum + 1 #colNum = colNum + 1 except: import sys print("Exception running PredictiveModel.writeSQLtoFile.", sys.exc_info()) conn.commit() # rs.close() conn.close() wb.save(filename)