def conn_to_rs(host, port, db, usr, pwd, opt=options, timeout=set_timeout_stmt):
rs_conn_string = """host=%s port=%s dbname=%s user=%s password=%s
%s""" % (host, port, db, usr, pwd, opt)
print "Connecting to %s:%s:%s as %s" % (host, port, db, usr)
rs_conn = DB(dbname=rs_conn_string)
rs_conn.query(timeout)
return rs_conn
def main(**kwargs):
CONFIG = configparser.ConfigParser()
CONFIG.read('db.cfg')
dbset = CONFIG['DBSETTINGS']
logger.info('Connecting to Database')
db = DB(dbname=dbset['database'],
host=dbset['host'],
user=dbset['user'],
passwd=dbset['password'])
proxies = {'https': kwargs.get('proxy', None)}
# Update Venue List
venues = []
curId = db.query('SELECT max(id) FROM city.venues').getresult()[0][0]
logger.info('Updating venues table')
venues, inserted_venues = update_venues(db, proxies, curId)
# Get Events from List of Venues
#cla = []
logger.info('Finished updating venues tables, %s new venues inserted',
inserted_venues)
inserted_count = update_events(db, proxies, venues)
logger.info('Finished processing events, %s events inserted',
inserted_count)
db.close()
def run_db_query(
*,
database: str,
host: str,
port: int,
username: str,
password: str,
query: str
) -> None:
logging.info("Initializing DB connection")
logging.info(
f"DB params dbname={database}, host={host}, "
f"port={port}, user={username}"
)
logging.info(f"query={query}")
db = DB(
dbname=database,
host=host,
port=port,
user=username,
passwd=password
)
logging.info("DB connection initialized")
logging.info("Executing query")
db.query(query)
logging.info("Query executed")
def __init__(self, dbname, dbhost, dbport, dbuser, dbpass):
try:
self.conn = DB(dbname=dbname,
host=dbhost,
port=dbport,
user=dbuser,
passwd=dbpass)
except Exception as ex:
print(ex)
self.conn = None
if (None != self.conn):
tables = self.conn.get_tables()
if (not ("public.users" in tables)):
try:
self.conn.query(
"CREATE TABLE users(uid bigint primary key, name text, bio text)"
)
except Exception as ex:
print(ex)
self.conn = None
if (not ("public.tweets" in tables)):
try:
self.conn.query(
"CREATE TABLE tweets(tid bigint primary key, author_id bigint, parent_id bigint, timestamp bigint, text text)"
)
except Exception as ex:
print(ex)
self.conn = None
def __init__(self):
DB.__init__(
self,
host=environ.get("PGHOST"),
dbname=environ.get("PGDBNAME"),
user=environ.get("PGUSER"),
passwd=environ.get("PGPASS"),
)
def get_data(from_table, key, value):
pg_db = DB(dbname=DATABASE_NAME,
host=DATABASE_URL,
port=int(DATABASE_PORT),
user=DATABASE_USERNAME,
passwd=DATABASE_PASSWORD)
result = pg_db.query("select " + key + " , " + value + " from " +
from_table + "")
return [convert_tuples(tuple, key, value) for tuple in result.getresult()]
def db_connect(self):
CONFIG = configparser.ConfigParser()
CONFIG.read('db.cfg')
dbset = CONFIG['DBSETTINGS']
self.db = DB(dbname=dbset['database'],
host=dbset['host'],
user=dbset['user'],
passwd=dbset['password'])
self.logger.info('Database connected.')
def get_csv_data():
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = DB(**params)
# execute a statement
print('PostgreSQL ALL accepted users who signed up for team matching')
q = conn.query(
"SELECT * FROM users JOIN event_applications ON users.id = event_applications.user_id WHERE custom_fields ->> 'team_forming' = 'Yes, sign me up!' AND status = 'accepted';"
)
data = q.dictresult()
f = StringIO()
writer = csv.writer(f,
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
writer.writerow(features)
print(f'Adding {len(data)} entries to csv file')
for row in data:
str_id = str(row['user_id'])
# get the MD5 hash of id
result = hashlib.md5(str_id.encode())
hashed_id = result.hexdigest()
full_duration = (row['custom_fields'].get(
'arrival_time', '') == "I'm staying for the entire event")
user_features = [hashed_id, row['first_name'], row['last_name'], row['email'], row['phone'], full_duration, \
row['age'], row['pronoun'], row['university'], row['education_lvl'], row['major'], \
row['grad_year'], row['custom_fields'].get("travel", None), row['custom_fields'].get("programming_skills", None), \
row['custom_fields'].get("been_to_ttb", None), None, None, row['custom_fields'].get("linkedin_url", None), \
row['custom_fields'].get("github_url", None), row['custom_fields'].get("other_url", None), row['custom_fields'].get("how_did_you_hear", None), \
None, row['custom_fields'].get("programming_experience", None), row['custom_fields'].get("how_many_hackathons", None), None, \
row['custom_fields'].get("other_skills", None), row['custom_fields'].get("particular_topic", None), row['custom_fields'].get("goals", None), \
row['custom_fields'].get("experience_area", None), row['custom_fields'].get("teammate_preference", None), None]
writer.writerow(user_features)
# move the pointer back to beginning of file
f.seek(0)
return f
except (Exception) as error:
print("Error:", error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
class SqlClient:
def __init__(self):
self.db = DB(dbname='refugee', host='localhost', user='******', passwd='1234')
def insert_into(self, fingerprint_hash, name, dob, gender):
res = self.db.query('INSERT INTO public.demographics(name, birth_date, fingerprint_hash, gender) \n VALUES(' + str(name) + ', ' + str(dob) + ', ' + str(fingerprint_hash) + ', ' + str(gender) + ');')
def lookUp(self, hash):
res = self.db.query(f'SELECT * FROM public.demographics WHERE fingerprint_hash == {hash}')
return res[0]
def __init__(self, arg):
#super(db_utils, self).__init__()
self.arg = arg
self.db = DB(dbname='structnet_complete',
user='******',
passwd='structnet',
host='localhost')
#self.identity = ''.join(random.choice(string.ascii_lowercase) for _ in range(8))
#print self.identity
print "Conncted!"
self.start_time = time.time()
def __init__(self, db_url):
urlparse.uses_netloc.append("postgres")
self.__db_url = db_url
url = urlparse.urlparse(db_url)
self.__db = DB(
dbname=url.path[1:],
user=url.username,
passwd=url.password,
host=url.hostname,
port=url.port
)
def __init__(self):
self.connection = DB(dbname=environ.get('POSTGRESQL_ADDON_DB'),
host=environ.get('POSTGRESQL_ADDON_HOST'),
port=int(environ.get('POSTGRESQL_ADDON_PORT')),
user=environ.get('POSTGRESQL_ADDON_USER'),
passwd=environ.get('POSTGRESQL_ADDON_PASSWORD'))
try:
self.connection.query("SELECT * FROM stats")
except ProgrammingError:
print "Create Table"
self.connection.query("""CREATE TABLE stats (timestamp TIMESTAMP PRIMARY KEY,value NUMERIC(5,2))""")
def connect(self):
if not self.pgsql:
raise ValueError('No coordinates to connect to the db.')
try:
self.db = DB(dbname=self.pgsql['user'],
host=self.pgsql['host'],
port=int(self.pgsql['port']),
user=self.pgsql['user'],
passwd=self.pgsql['password'])
except:
raise IOError('Could not connect to the db.')
self.status = "Connected"
def read_line():
i = 0
reader = csv.reader(f, delimiter='\t')
db = DB(dbname="ngram" ,user="******" , port=5432)
for row in reader:
#ngram, year, match_count, page_count, volume_count
sql = sqlp1 + " " + tb_name + " " + sqlp2 + " (" + str(i) + ", " \
+ "\'" + row[0] + "\'" + ", " + row[1] + ", " + row[2] + ", " + row[3] \
+ ", " + row[4] + ");";
print sql
i = i + 1
#call insert(sql)
db.query(sql)
db.close()
def insert_data(data: list, dbset: dict, live: bool):
'''
Upload data to the database
:param data:
List of dictionaries, gets converted to list of tuples
:param dbset:
DB settings passed to Pygresql to create a connection
'''
num_rows = len(data)
if num_rows > 0:
LOGGER.info('Uploading %s rows to PostgreSQL', len(data))
LOGGER.debug(data[0])
else:
LOGGER.warning('No data to upload')
return
to_insert = []
for dic in data:
# convert each observation dictionary into a tuple row for inserting
row = (dic["userId"], dic["analysisId"], dic["measuredTime"],
dic["measuredTimeNoFilter"], dic["startPointNumber"],
dic["startPointName"], dic["endPointNumber"],
dic["endPointName"], dic["measuredTimeTimestamp"],
dic["outlierLevel"], dic["cod"], dic["deviceClass"])
to_insert.append(row)
db = DB(**dbset)
if live:
db.inserttable('king_pilot.daily_raw_bt', to_insert)
else:
db.inserttable('bluetooth.raw_data', to_insert)
db.close()
def healthcheck(self):
self._generic_healthcheck()
spec = self.get_spec()
env = {e['name']: e['value'] for e in spec['containers'][0]['env']}
user = env['POSTGRES_USER']
passwd = env['POSTGRES_PASSWORD']
db = DB(dbname=user,
host=self.host,
port=5432,
user=user,
passwd=passwd)
sql = "create table test_table(id serial primary key, name varchar)"
db.query(sql)
assert_in('public.test_table', db.get_tables())
def main():
#enter screens
dup_fp = open("duplicates.txt", "a")
mm_fp = open("mismatches.txt","a")
processed_fp = open("processed_folders.txt", 'a')
os.chdir("Screens")
extracted_patient_tuples = []
extracted_patient_nonmatches = []
print(os.getcwd())
for folder in sorted(os.listdir(os.getcwd()), key=keyfunc):
folder_path = os.path.join(os.getcwd(),folder)
print(folder_path)
for image in os.listdir(folder_path):
#call to ocr
text = OCR_img(Image.open(os.path.join(folder_path, image)))
#append to the list of found patients in this folder
[extracted_patient_tuples.append(element) for element in extract_patient_info(text)[0]]
[extracted_patient_nonmatches.append(element) for element in extract_patient_info(text)[1]]
extracted_patient_tuples = list(set(extracted_patient_tuples))
os.chdir("..")
[mm_fp.write(element + "\n") for element in extracted_patient_nonmatches]
insert_patient_set_into_db(DB("patient_data"), dup_fp, extracted_patient_tuples)
processed_fp.write(folder+'\n')
extracted_patient_tuples = []
extracted_patient_nonmatches = []
os.chdir("Screens")
return
def __init__(self, maxusage=None, setsession=None, closeable=True,
*args, **kwargs):
"""Create a "tough" PostgreSQL connection.
maxusage: maximum usage limit for the underlying PyGreSQL connection
(number of uses, 0 or None means unlimited usage)
When this limit is reached, the connection is automatically reset.
setsession: optional list of SQL commands that may serve to prepare
the session, e.g. ["set datestyle to ...", "set time zone ..."]
closeable: if this is set to false, then closing the connection will
be silently ignored, but by default the connection can be closed
args, kwargs: the parameters that shall be used to establish
the PostgreSQL connections with PyGreSQL using pg.DB()
"""
# basic initialization to make finalizer work
self._con = None
self._closed = True
# proper initialization of the connection
if maxusage is None:
maxusage = 0
if not isinstance(maxusage, int):
raise TypeError("'maxusage' must be an integer value.")
self._maxusage = maxusage
self._setsession_sql = setsession
self._closeable = closeable
self._con = PgConnection(*args, **kwargs)
self._transaction = False
self._closed = False
self._setsession()
self._usage = 0
def on_chose(self, entry):
"""
Tries to connect to the given database entry selected from
the SelectableText widget. The entry argument is an Entry object
from the pgpasslib.
"""
logging.info('Connecting to database...')
self.status_text.set_text('Connecting to database %s...' % entry.dbname)
self.footer.set_attr_map({ None: 'footer' })
try:
connection = DB(
dbname=entry.dbname,
host=entry.host,
port=entry.port,
user=entry.user,
passwd=entry.password
)
logging.info('Connected to databse %s' % entry.dbname)
urwid.emit_signal(self, 'connected', connection)
except Exception as e:
logging.error(str(e).strip())
self.status_text.set_text(str(e).strip())
self.footer.set_attr_map({ None: 'footer_error' })
class db_query:
def __init__(self, auth):
self.auth = auth
self.connect()
def __del__(self):
try:
self.db.close()
except:
pass
def connect(self):
self.db = DB(dbname=self.auth['db'],
host=self.auth['host'],
port=self.auth['port'],
user=self.auth['user'],
passwd=self.auth['passwd'])
def run_query(self, query, *args):
def send_query(q):
return self.db.query(q)
try:
res = send_query(query)
except:
self.connect()
res = send_query(query)
return res
def array_query(self, query, *args):
res = self.run_query(query)
return res.dictresult()
def main(): #enlever token
#token = 'EAACEdEose0cBADbHU92C4fP13NnLV9ZB7x7qqRiuF5W3fw6ZBZCFZBlx3QhuhgObNiGAJHL36pw8ujwLKkJAUwEIFJQ4zZAilMw3skQ5wOQLP3vk1ZBDgTDM8pbsNeMClB4Fla1EH6P3AiEwlIANC34SyZCSWGUTdsSZBaEWPyyB9K5ZAVDOZAFpOGetpKZCKhhIA8ZD'
token = request.args.get('token')
#return token
db = DB(dbname='postgres',
host='172.17.0.4',
port=5432,
user='******',
passwd='admin')
myFacebook = FacebookAPI(access_token=token)
likes = myFacebook.getNamesFromFacebookLikes()
dict_reponse = {}
for like in likes:
like = like.replace('"', '') #fait planter le front sinon
like = like.replace('\'', '') #fait planter le front sinon
rep = fetchDataSingle(db, like)
# return json.dumps(rep)
dict_reponse[like] = rep
reponse = Response("")
reponse.headers['Access-Control-Allow-Origin'] = '*'
reponse.data = json.dumps(dict_reponse)
reponse.mimetype = "json"
return reponse
def cancel(self):
# assignment is an atomic operation in python
self.cancel_flag = True
# if self.conn is not set we cannot cancel.
if self.cancel_conn:
DB(self.cancel_conn).cancel()
def connect():
"""
Connects to the database using data from dbConfig.py.
Returns:
Handler of database connection.
"""
return DB(dbname=DBNAME, host=HOST, user=USER, passwd=PASSWD)
def connect():
db = DB(dbname='sislogucab',
user='******',
passwd='root',
host='localhost',
port=5432)
return db
def requestDB(requete='select * from ',tableName='planet_osm_line',base='champ',
limit=None,only=False,verbose=False):
"""
Function that execute an sql request into a postgres database.
---
parameters :
string requete : the request will be executed.
string tableName : the name of the table will be request if only is false.
string base : name of the database
int limit : integer, request limit option.
produces :
string requeteResult : request result.
"""
db = DB(dbname=base)
if verbose :
tablesList = db.get_tables()
print('Tables :')
printPuces(tablesList)
print('\n')
headerLinesOSM = db.get_attnames(tableName)
print('Table header :')
printPuces(headerLinesOSM)
print('\n')
if only :
requeteResult = db.query(requete).getresult()
elif limit is None :
requeteResult = db.query(requete+tableName).getresult()
else :
requeteResult = db.query(requete+tableName+' limit '+str(limit)).getresult()
return requeteResult
def connect(parameters_path):
with open(parameters_path, 'r') as fich_p:
parameters = simplejson.loads(fich_p.read())
db = DB(dbname=parameters['dbname'],
host=parameters['dbserver'],
port=parameters['dbport'],
user=parameters['dbuser'],
passwd=parameters['dbpass'])
return db
def db_connect():
CONFIG = configparser.ConfigParser()
CONFIG.read('db.cfg')
dbset = CONFIG['DBSETTINGS']
db = DB(dbname=dbset['database'],
host=dbset['host'],
user=dbset['user'],
passwd=dbset['password'])
return db
def get_db():
dbname = app.config['DBNAME']
user = app.config['USER']
passwd = app.config['PASSWD']
if not hasattr(g, 'psql_db'):
g.psql_db = DB(dbname=dbname, user=user, passwd=passwd)
return g.psql_db
def connect_to_db(description):
dbname = description['dbname']
host = description['host']
port = description['port']
opt = description['opt']
user = description['user']
passwd = description['passwd']
db = DB(dbname, host, port, opt, user, passwd)
return db
def __init__(self, conn_object: dict = None):
if conn_object is None:
raise AttributeError(
"You are missing the conn_object. Use the QueryService to retrieve the object."
)
self.dbname = conn_object['dbName']
self.host = conn_object['host']
self.port = conn_object['port']
self.user = conn_object['username']
self.passwd = conn_object['token']
self.websocketHost = conn_object['websocketHost']
self.config_object = {
"dbname": self.dbname,
"host": self.host,
"user": self.user,
"passwd": self.passwd,
"port": self.port,
}
self.db = DB(**self.config_object)
def connect():
parser = argparse.ArgumentParser()
parser.add_argument('--host')
parser.add_argument('--user')
parser.add_argument('--passwd')
parser.add_argument('--schema', default = 'mimiciii')
parser.add_argument('--mode', default = 'all')
args = parser.parse_args()
# host = '162.105.146.246'
# host = 'localhost'
# schema = 'mimiciii'
host = args.host
user = args.user
passwd = args.passwd
logging.info('connect to %s, user = %s, search_path = %s' %(host, user, args.schema))
db = DB(host = host, user = user, passwd = passwd)
db.query('set search_path to %s' %(args.schema))
return db
def __init__(self):
"""Connecting to localhost (default host and port [localhost, 4532]) PostGre DB and initializing needed data
base and tables."""
try:
print("Connecting to PostGre DB...")
self.__db = DB(dbname='testdb', host='localhost', port=5432, user='******', passwd='superuser')
print("PostGre DB connection successfully built.")
except ConnectionError:
print("PostGre DB connection could not be built.")
self.delete_all_data()
self.drop_all_tables()
def read_data(device_id: str, temp: Optional[float], humid: Optional[float],
light: Optional[float]):
db = DB(dbname=dbname,
host=dbhost,
port=dbport,
user=dbuser,
passwd=dbpassword)
table = "raw_data"
local_received_time = datetime.datetime.utcnow()
insert_statement = f"""INSERT INTO {table} (device_id,temp,humidity,light,timestamp_on_write)
VALUES ('{device_id}',{temp},{humid},{light},'{local_received_time}');
"""
# Write to PG DB
try:
db.query(insert_statement)
except:
pass
# Push to Power BI if feature flag is True
if stream_pb == True:
try:
payload = f"""
[{{
"DateTime" :"{local_received_time}",
"Temperature" :{temp},
"Humidity" :{humid},
"Brightness" :{light},
"Device" :"{device_id}"
}}]
"""
body = bytes(payload, encoding='utf-8')
http_req = urllib2.Request(streaming_url, body)
response = urllib2.urlopen(http_req)
except:
print("Power BI ERROR")
return ("received")
def getSchemaInfo( options, sql_tables, sql_references ): """ Connect to the database and retrieve our schema information. """ conn = DB( options[ 'dbname' ], options[ 'dbhost' ], int( options[ 'dbport' ] ), user=options[ 'dbuser' ] ) res = conn.query( options[ 'query' ] ).dictresult() for i in range( len( res ) ): ftbl = res[i][ 'table_name' ] fcol = res[i][ 'column_name' ] type = res[i][ 'data_type' ] nullable = res[i][ 'is_nullable' ] keytype = res[i][ 'constraint_type' ] ttbl = res[i][ 'referenced_table_name' ] tcol = res[i][ 'referenced_column_name' ] if not sql_tables.has_key( ftbl ): sql_tables[ ftbl ] = [] sql_tables[ ftbl ] += [ [ fcol, type, nullable, keytype ] ] if keytype == 'FOREIGN KEY' : sql_references += [ [ ftbl, fcol, ttbl, tcol ] ]
class PGDB(object):
def __init__(self):
pass
def connect(self, dbname, host, port, user):
self.db = DB(dbname=dbname, host=host, port=port, user=user)
def connect_default(self):
self.db = DB(dbname=DBNAME, host=HOST, port=PORT, user=USER)
def close(self):
self.db.close()
def execute(self, sql):
return self.db.query(sql)
def drop_table(self, tbl):
self.execute('DROP TABLE if exists %s' % tbl)
def create_init_table(self, tbl):
self.execute('CREATE TABLE %s (uid INT, iid INT, rating REAL)' % tbl)
def copy(self, tbl, path, delimiter):
self.execute("COPY %s FROM '%s' delimiter '%s'" % (tbl, path, delimiter))
def __init__(self, maxusage=0, setsession=None, *args, **kwargs): """Create a "tough" PostgreSQL connection. maxusage: maximum usage limit for the underlying PygreSQL connection (number of uses, 0 or False means unlimited usage) When this limit is reached, the connection is automatically reset. setsession: optional list of SQL commands that may serve to prepare the session, e.g. ["set datestyle to ...", "set time zone ..."] args, kwargs: the parameters that shall be used to establish the PostgreSQL connections with PyGreSQL using pg.DB() """ self._maxusage = maxusage self._setsession_sql = setsession self._closeable = 1 self._usage = 0 self._con = PgConnection(*args, **kwargs) self._setsession()
def db_input(tweet):
tweet = tweet.split(" ");
hashtags = []
words = []
for word in tweet:
if len(word) > 3 and special_match(word, plaintext) == True:
word = word.lower()
if "#" == word[0]:
hashtags.append(word)
else:
words.append(word)
insert_update = ("with w as (insert into words (word) values ('{}') on conflict (word) do update set word = words.word returning (wid)), h as (insert into hashtags (hashtag) values ('{}') on conflict (hashtag) do update set hashtag = hashtags.hashtag returning (hid)) insert into links (wid, hid, weight) values ((select * from w), (select * from h), 1) on conflict (wid, hid) do update set weight = links.weight + 1")
db = DB(dbname=database, host=host, post=int(port), user=user, passwd=password)
db.begin()
for word in words:
for hashtag in hashtags:
db.query(insert_update.format(word, hashtag))
db.commit()
class SteadyPgConnection:
"""Class representing steady connections to a PostgreSQL database.
Underlying the connection is a classic PyGreSQL pg API database
connection which is reset if the connection is lost or used too often.
Thus the resulting connection is steadier ("tough and self-healing").
If you want the connection to be persistent in a threaded environment,
then you should not deal with this class directly, but use either the
PooledPg module or the PersistentPg module to get the connections.
"""
version = __version__
def __init__(self, maxusage=None, setsession=None, closeable=True,
*args, **kwargs):
"""Create a "tough" PostgreSQL connection.
maxusage: maximum usage limit for the underlying PyGreSQL connection
(number of uses, 0 or None means unlimited usage)
When this limit is reached, the connection is automatically reset.
setsession: optional list of SQL commands that may serve to prepare
the session, e.g. ["set datestyle to ...", "set time zone ..."]
closeable: if this is set to false, then closing the connection will
be silently ignored, but by default the connection can be closed
args, kwargs: the parameters that shall be used to establish
the PostgreSQL connections with PyGreSQL using pg.DB()
"""
# basic initialization to make finalizer work
self._con = None
self._closed = True
# proper initialization of the connection
if maxusage is None:
maxusage = 0
if not isinstance(maxusage, int):
raise TypeError("'maxusage' must be an integer value.")
self._maxusage = maxusage
self._setsession_sql = setsession
self._closeable = closeable
self._con = PgConnection(*args, **kwargs)
self._transaction = False
self._closed = False
self._setsession()
self._usage = 0
def _setsession(self):
"""Execute the SQL commands for session preparation."""
if self._setsession_sql:
for sql in self._setsession_sql:
self._con.query(sql)
def _close(self):
"""Close the tough connection.
You can always close a tough connection with this method
and it will not complain if you close it more than once.
"""
if not self._closed:
try:
self._con.close()
except Exception:
pass
self._transaction = False
self._closed = True
def close(self):
"""Close the tough connection.
You are allowed to close a tough connection by default
and it will not complain if you close it more than once.
You can disallow closing connections by setting
the closeable parameter to something false. In this case,
closing tough connections will be silently ignored.
"""
if self._closeable:
self._close()
elif self._transaction:
self.reset()
def reopen(self):
"""Reopen the tough connection.
It will not complain if the connection cannot be reopened.
"""
try:
self._con.reopen()
except Exception:
if self._transcation:
self._transaction = False
try:
self._con.query('rollback')
except Exception:
pass
else:
self._transaction = False
self._closed = False
self._setsession()
self._usage = 0
def reset(self):
"""Reset the tough connection.
If a reset is not possible, tries to reopen the connection.
It will not complain if the connection is already closed.
"""
try:
self._con.reset()
self._transaction = False
self._setsession()
self._usage = 0
except Exception:
try:
self.reopen()
except Exception:
try:
self.rollback()
except Exception:
pass
def begin(self, sql=None):
"""Begin a transaction."""
self._transaction = True
try:
begin = self._con.begin
except AttributeError:
return self._con.query(sql or 'begin')
else:
# use existing method if available
if sql:
return begin(sql=sql)
else:
return begin()
def end(self, sql=None):
"""Commit the current transaction."""
self._transaction = False
try:
end = self._con.end
except AttributeError:
return self._con.query(sql or 'end')
else:
if sql:
return end(sql=sql)
else:
return end()
def commit(self, sql=None):
"""Commit the current transaction."""
self._transaction = False
try:
commit = self._con.commit
except AttributeError:
return self._con.query(sql or 'commit')
else:
if sql:
return commit(sql=sql)
else:
return commit()
def rollback(self, sql=None):
"""Rollback the current transaction."""
self._transaction = False
try:
rollback = self._con.rollback
except AttributeError:
return self._con.query(sql or 'rollback')
else:
if sql:
return rollback(sql=sql)
else:
return rollback()
def _get_tough_method(self, method):
"""Return a "tough" version of a connection class method.
The tough version checks whether the connection is bad (lost)
and automatically and transparently tries to reset the connection
if this is the case (for instance, the database has been restarted).
"""
def tough_method(*args, **kwargs):
transaction = self._transaction
if not transaction:
try: # check whether connection status is bad
if not self._con.db.status:
raise AttributeError
if self._maxusage: # or connection used too often
if self._usage >= self._maxusage:
raise AttributeError
except Exception:
self.reset() # then reset the connection
try:
result = method(*args, **kwargs) # try connection method
except Exception: # error in query
if transaction: # inside a transaction
self._transaction = False
raise # propagate the error
elif self._con.db.status: # if it was not a connection problem
raise # then propagate the error
else: # otherwise
self.reset() # reset the connection
result = method(*args, **kwargs) # and try one more time
self._usage += 1
return result
return tough_method
def __getattr__(self, name):
"""Inherit the members of the standard connection class.
Some methods are made "tougher" than in the standard version.
"""
if self._con:
attr = getattr(self._con, name)
if (name in ('query', 'get', 'insert', 'update', 'delete')
or name.startswith('get_')):
attr = self._get_tough_method(attr)
return attr
else:
raise InvalidConnection
def __del__(self):
"""Delete the steady connection."""
try:
self._close() # make sure the connection is closed
except Exception:
pass
import json
import pymongo
from pg import DB
pgcnn=DB(dbname='osm',host='localhost',port=5432,user='******',passwd='1')
mgcnn=pymongo.MongoClient('192.168.111.250',27017)
cndata=mgcnn.china.data
#cndata.remove({'device_table':'public.road_label_layer'})
def genSql(tbname):
global pgcnn
sql = "select column_name,data_type from information_schema.columns where table_schema='public' and table_name = '%s'" % tbname
columns = pgcnn.query(sql).namedresult()
cols='select row_to_json(tb) as json from (select '
for tup in columns:
name = tup.column_name
if(name!='zscale'):
if(name == 'geometry'):
name = 'st_asgeojson(st_transform(geometry,4326))as geometry'
elif(name == 'wkb_geometry'):
name = 'st_asgeojson(st_transform(wkb_geometry,4326))as geometry'
cols+=name+','
cols = cols.rstrip(',')
cols += ' from %s tb;' % tbname
return cols
pgtbs=pgcnn.query("select tablename from pg_tables where schemaname='public' and tablename like '%_layer' order by tablename;")
tbrows=pgtbs.namedresult()
#tbrows=['road_layer']
for row in tbrows:
tbname=row.tablename
print(tbname)
#!usr/bin/python
# -*- coding: utf-8 -*-
#apt-get install python-xlrd
import xlrd
from pg import DB
import sys
type = sys.getfilesystemencoding()
print(type)
db=DB(dbname='postgres',host='localhost',port=5432,user='******',passwd='postgres')
xl=xlrd.open_workbook(u'电大所-海银御景站-管网.xls')
#xl=xlrd.open_workbook('/home/bblu/repo/algopython/database/pipe.xls')
sheetname = xl.sheet_names()
xl_sheet = xl.sheet_by_index(0)
print xl_sheet.name
xl_sheet1 = xl.sheet_by_name(u'Sheet1')
print xl_sheet1.name
fileds=""
for col in range(0,xl_sheet.ncols):
fileds+=xl_sheet.cell(0,col).value+','
print(fileds)
for row in range(1,xl_sheet.nrows):
values=""
#这里可以选择必要的两三个字段看看在第几列
#values+= xl_sheet.cell(row,col).value + ','
for col in range(0,xl_sheet.ncols):
values+= unicode(xl_sheet.cell(row,col).value) + ','
def connect_default(self):
self.db = DB(dbname=DBNAME, host=HOST, port=PORT, user=USER)
# -*- coding: utf-8 -*-
"""
insert data to PostgreSQL
@author: Shih-Chi
"""
from pg import DB
import numpy as np
import datetime
db = DB(dbname='Pingtung', host='localhost', port=5432,
user='******', passwd='husky')
sites = db.query('select site from "Well_Stations"').getresult()
for site in sites:
mindate = db.query('select MIN(date),MAX(date) from "Well" where site like \'{site}\''.format(site = site[0])).getresult()
if mindate[0][0] != None:
print('{site}: date from {ys}/{ms}/{ds} to {ye}/{me}/{de}'.format(site=site[0],ys=mindate[0][0].year,ms=mindate[0][0].month,ds=mindate[0][0].day,ye=mindate[0][1].year,me=mindate[0][1].month,de=mindate[0][1].day))
db.query('UPDATE "Well_Stations" SET starttime = \'{ys}/{ms}/{ds}\', endtime = \'{ye}/{me}/{de}\' WHERE site like \'{site}\''.format(site=site[0],ys=mindate[0][0].year,ms=mindate[0][0].month,ds=mindate[0][0].day,ye=mindate[0][1].year,me=mindate[0][1].month,de=mindate[0][1].day));
from pg import DB
PG=DB(dbname='VM', host='192.168.1.112' ,port=5432,user='******',passwd='123456')
sql="select A.sample_time,A.stat_name,B.stat_rollup,B.unit,A.stat_group,A.entity,A.stat_value from hist_stat_daily A ,stat_counters B where A.stat_id=B.id "
q=PG.query(sql)
rows=q.getresult()
for row in rows:
PG.insert("vm_state",time=row[0],stat_name=row[1],stat_rollup_type=row[2],unit=row[3],stat_group=row[4],entity=row[5],stat_value=row[6])
print("成功")
PG.close()
class PostGreDBConnector:
"""PostGreDBConnector opens a PostGre DB connection. Different functions allow you to add, delete or update
documents in PostGre DB."""
def __init__(self):
"""Connecting to localhost (default host and port [localhost, 4532]) PostGre DB and initializing needed data
base and tables."""
try:
print("Connecting to PostGre DB...")
self.__db = DB(dbname='testdb', host='localhost', port=5432, user='******', passwd='superuser')
print("PostGre DB connection successfully built.")
except ConnectionError:
print("PostGre DB connection could not be built.")
self.delete_all_data()
self.drop_all_tables()
def close_connection(self):
self.__db.close()
def create_schema(self, schema_name):
self.__db.query("CREATE SCHEMA " + schema_name)
self.__create_tables(schema_name)
self.__create_functions(schema_name)
def __create_tables(self, schema):
"""Create needed tables for RDF parsing."""
schema += "."
self._add_table("CREATE TABLE " + schema + "texts (id serial primary key, title text)")
self._add_table(
"CREATE TABLE " + schema + "bscale (id serial primary key, bscale text, nominal bool, ordinal bool, interval bool)")
self._add_table("CREATE TABLE " + schema + "bsort (id serial primary key, bsort text)")
self._add_table("CREATE TABLE " + schema + "pattern (id serial primary key, pattern text)")
self._add_table("CREATE TABLE " + schema + "single_pattern (id serial primary key, single_pattern text)")
self._add_table("CREATE TABLE " + schema + "snippets (id serial primary key, snippet text)")
# relations
self._add_table("CREATE TABLE " + schema + "has_attribute (bsort_id int, bscale_id integer[], aggregation int)")
self._add_table("CREATE TABLE " + schema + "has_object (bscale_id int, pattern_id integer[], aggregation int)")
self._add_table(
"CREATE TABLE " + schema + "pattern_single_pattern (pattern_id int, single_pattern_id integer[], aggregation int)")
self._add_table("CREATE TABLE " + schema + "texts_snippets (text_id int primary key, snippet_id integer[], aggregation int)")
self._add_table(
"CREATE TABLE " + schema + "snippet_offsets (id serial primary key,"
" single_pattern_id int, snippet_id int, offsets integer[][], aggregation int)")
# adjective and verb extractions
self._add_table("CREATE TABLE " + schema + "subject_occ (id serial primary key, subject text, count int)")
self._add_table("CREATE TABLE " + schema + "adjective_occ (id serial primary key, adjective text, count int)")
self._add_table("CREATE TABLE " + schema + "verb_occ (id serial primary key, verb text, count int)")
self._add_table("CREATE TABLE " + schema + "object_occ (id serial primary key, object text, count int)")
self._add_table("CREATE TABLE " + schema + "subject_adjective_occ (id serial primary key, subject int, adjective int, count int, pmi float)")
self._add_table("CREATE TABLE " + schema + "subject_object_occ (id serial primary key, subject int, object int, count int, pmi float)")
self._add_table("CREATE TABLE " + schema + "object_verb_occ (id serial primary key, object int, verb int, count int, pmi float)")
self._add_table("CREATE TABLE " + schema + "subject_verb_occ (id serial primary key, subject int, verb int, count int, pmi float)")
# correlating pattern
self._add_table("CREATE TABLE " + schema + "bscale_single_pattern (id serial primary key, bscale_id int, single_pattern_id int, single_pattern text, count int)")
self._add_table(
"CREATE TABLE " + schema + "correlating_pattern (id serial primary key, pattern_a int, pattern_b int, count int, pmi float)")
def __create_functions(self, schema):
"""Create all necessary functions to aggregate the results saved in the database."""
schema += "."
self.add_function(schema + "aggregate_texts_snippets", "SELECT text_id, array_length(snippet_id, 1) FROM " + schema + "texts_snippets")
self.add_function(schema + "aggregate_snippet_offsets", "SELECT id, array_length(offsets, 1) FROM " + schema + "snippet_offsets")
def add_function(self, name, function):
"""Create a new function in the db."""
create_function = "CREATE FUNCTION "
returns = "() RETURNS SETOF RECORD AS "
lang = " LANGUAGE SQL"
query = create_function + name + returns + add_quotes(function) + lang
self.__db.query(query)
def _add_table(self, query):
"""Create a new table with a query."""
self.__db.query(query)
def add_table(self, schema, name, rows):
"""Create a new table with a name and rows given in query form."""
create_table = "CREATE TABLE "
query = create_table + schema + "." + name + rows
self.__db.query(query)
def insert(self, schema, table, row):
"""Insert a new row element into a specified table."""
return self.__db.insert(schema + "." + table, row)
def is_in_table(self, schema, table, where_clause):
"""Returns whether a row already exists in a table or not."""
select = "SELECT * FROM "
where = " WHERE "
q = select + schema + "." + table + where + where_clause
result = self.__db.query(q).dictresult()
if len(result) > 0:
return True
else:
return False
def update(self, schema, table, values, where_clause):
"""Update an entry in a specified table."""
UPDATE = "UPDATE "
SET = " SET "
WHERE = " WHERE "
query = UPDATE + schema + "." + table + SET + values + WHERE + where_clause
self.query(query)
def get(self, schema, table, where_clause, key):
"""Return the key of a specific item in a table."""
select = "SELECT "
_from = " FROM "
where = " WHERE "
q = select + key + _from + schema + "." + table + where + where_clause
result = self.__db.query(q).dictresult()
if len(result) > 0:
return result[0][key]
else:
return None
def get_data_from_table(self, schema, table):
"""Gets all data available in a specific table."""
return self.__db.query("SELECT * FROM " + schema + "." + table).dictresult()
def get_id(self, schema, table, where_clause):
"""Return the id of an item in a table. If found return id number of found item, else None."""
select = "SELECT id FROM "
where = " WHERE "
q = select + schema + "." + table + where + where_clause
result = self.__db.query(q).dictresult()
if len(result) > 0:
return result[0]['id']
else:
return None
def delete_from_table(self, schema, table, row):
"""Delete a row element form a specific table."""
return self.__db.delete(schema + "." + table, row)
def delete_data_in_table(self, schema, table):
"""Delete all data in a specific table."""
self.__db.truncate(schema + "." + table, restart=True, cascade=True, only=False)
def delete_all_data(self):
"""Deletes all data from all existing tables."""
tables = self.get_tables()
for table in tables:
table_name = str(table)
self.__db.truncate(table_name, restart=True, cascade=True, only=False)
def get_tables(self):
"""Get all available tables in the database."""
return self.__db.get_tables()
def get_attributes(self, schema, table):
"""Get all attributes of a specified table."""
return self.__db.get_attnames(schema + "." + table)
def drop_table(self, schema, table):
"""Drops a specified table."""
query = "DROP TABLE "
self.__db.query(query + schema + "." + table)
def drop_all_tables(self):
"""Drops all existing tables."""
tables = self.get_tables()
table_names = ""
if len(tables) > 0 :
for ind, table in enumerate(tables):
if ind == 0:
table_names = str(table)
else:
table_names = table_names + ", " + str(table)
self.__db.query("DROP TABLE " + table_names)
else:
print("Nothing to delete.")
def get_all(self, schema, table, attribute):
"""Gets one or more attributes of all entries from a specified table."""
select = "SELECT "
_from = " FROM "
query = select + attribute + _from + schema + "." + table
return self.__db.query(query).dictresult()
def query(self, query):
"""Sends a query to the database."""
result = self.__db.query(query)
if result is not None:
if not isinstance(result, str):
return result.dictresult()
else:
return result
from pg import DB
db=DB(dbname='gis',host='localhost',port=5432,user='******',passwd='pswd')
# 1.drop tables begin with 't_'
q=db.query("SELECT tablename FROM pg_tables WHERE schemaname='public' and tablename LIKE 't\_%'")
rows=q.namedresult()
c=0;
for row in rows:
tbname=row.tablename
alt='ALTER TABLE %s ADD COLUMN' % tbname
sql='%s edittype_update_ integer' % alt
sql='%s; %s edittime_update_ timestamp with time zone'%(sql,alt)
sql='%s; %s editversionid_update_ bigint;'%(sql,alt)
db.query(sql)
c=c+1
print('%d : %s' %(c,sql))
db.close()
#清除 variable explorer，避免汙染
from IPython import get_ipython
get_ipython().magic('reset -sf')
"""
Plot Well data and Precipitation data
@author: Shih-Chi
"""
from matplotlib import dates #繪圖時的時間處理
from pylab import * #matplotlib 接口
from pg import DB
import numpy as np
import os #資料夾
import sys #file
db = DB(dbname='Pingtung', host='localhost', port=5432, user='******', passwd='husky')
eventtime = [datetime.date(2007, 6, 5), datetime.date(2007, 6, 14)]
ROOT = r'E:\work\水利署計畫\result'
newfolder = '{ST}_{ET}'.format( ST = str(eventtime[0]), ET = str(eventtime[1]))
newpath = os.path.join(ROOT, newfolder)
if not os.path.exists(newpath):
os.makedirs(newpath)
os.makedirs(os.path.join(newpath, 'P'))
os.makedirs(os.path.join(newpath, 'well'))
file = open(os.path.join(newpath, 'P/累積雨量.csv'), 'w')
import sys
from itertools import chain
from suds.client import Client
from pg import DB
import time
reload(sys)
sys.setdefaultencoding("utf8")
start_time = time.time()
url = "http://www.marinespecies.org/aphia.php?p=soap&wsdl=1"
client = Client(url)
db = DB(dbname="gbif", host="localhost", port=5432, user="******", passwd="postgres")
offset = 0
while True:
names = db.query("select name from gbif.names where match_type is null limit 50 offset " + str(offset)).getresult()
names = list(chain(*names))
if (len(names) == 0):
break
scientificnames = client.factory.create("scientificnames")
scientificnames.item = names
scientificnames._arrayType = "xsd:string[]"
try:
results = client.service.matchAphiaRecordsByNames(scientificnames, True, True, False)
except:
e = sys.exc_info()[0]
print "Error: " + str(e)
#!/usr/bin/env python
import sys
import yaml
import json
from pg import DB
bosh_manifest_path = sys.argv[1]
with open(bosh_manifest_path, 'r') as f:
bosh_manifest = yaml.load(f.read())
settings_path = sys.argv[2]
with open(settings_path, 'r') as f:
cf_ip = json.loads(f.read())['cf-ip']
postgres_properties = bosh_manifest['jobs'][0]['properties']['postgres']
dbname = postgres_properties.get('database')
host = postgres_properties.get('host')
port = postgres_properties.get('port', 5432)
user = postgres_properties.get('user')
passwd = postgres_properties.get('password')
db = DB(dbname=dbname, host=host, port=port, user=user, passwd=passwd)
domain_id = db.insert('domains', name='xip.io', type='NATIVE')['id']
db.insert('records', domain_id=domain_id, name='{0}.xip.io'.format(cf_ip), content='localhost [email protected] 1', type='SOA', ttl=86400, prio=None)
db.insert('records', domain_id=domain_id, name='{0}.xip.io'.format(cf_ip), content='dns-us1.powerdns.net', type='NS', ttl=86400, prio=None)
db.insert('records', domain_id=domain_id, name='*.{0}.xip.io'.format(cf_ip), content=cf_ip, type='A', ttl=120, prio=None)
db.close()
class SteadyPgConnection:
"""Class representing steady connections to a PostgreSQL database.
Underlying the connection is a classic PyGreSQL pg API database
connection which is reset if the connection is lost or used too often.
Thus the resulting connection is steadier ("tough and self-healing").
If you want the connection to be persistent in a threaded environment,
then you should not deal with this class directly, but use either the
PooledPg module or the PersistentPg module to get the connections.
"""
def __init__(self, maxusage=0, setsession=None, *args, **kwargs):
"""Create a "tough" PostgreSQL connection.
maxusage: maximum usage limit for the underlying PygreSQL connection
(number of uses, 0 or False means unlimited usage)
When this limit is reached, the connection is automatically reset.
setsession: optional list of SQL commands that may serve to prepare
the session, e.g. ["set datestyle to ...", "set time zone ..."]
args, kwargs: the parameters that shall be used to establish
the PostgreSQL connections with PyGreSQL using pg.DB()
"""
self._maxusage = maxusage
self._setsession_sql = setsession
self._closeable = 1
self._usage = 0
self._con = PgConnection(*args, **kwargs)
self._setsession()
def _setsession(self):
"""Execute the SQL commands for session preparation."""
if self._setsession_sql:
for sql in self._setsession_sql:
self._con.query(sql)
def _close(self):
"""Close the tough connection.
You can always close a tough connection with this method
and it will not complain if you close it more than once.
"""
try:
self._con.close()
self._usage = 0
except:
pass
def close(self):
"""Close the tough connection.
You are allowed to close a tough connection by default
and it will not complain if you close it more than once.
You can disallow closing connections by setting
the _closeable attribute to 0 or False. In this case,
closing a connection will be silently ignored.
"""
if self._closeable:
self._close()
def reopen(self):
"""Reopen the tough connection.
It will not complain if the connection cannot be reopened."""
try:
self._con.reopen()
self._setsession()
self._usage = 0
except:
pass
def reset(self):
"""Reset the tough connection.
If a reset is not possible, tries to reopen the connection.
It will not complain if the connection is already closed.
"""
try:
self._con.reset()
self._setsession()
self._usage = 0
except:
self.reopen()
def _get_tough_method(self, method):
"""Return a "tough" version of a connection class method.
The tough version checks whether the connection is bad (lost)
and automatically and transparently tries to reset the connection
if this is the case (for instance, the database has been restarted).
"""
def tough_method(*args, **kwargs):
try: # check whether connection status is bad
if not self._con.db.status:
raise AttributeError
if self._maxusage: # or connection used too often
if self._usage >= self._maxusage:
raise AttributeError
except:
self.reset() # then reset the connection
try:
r = method(*args, **kwargs) # try connection method
except: # error in query
if self._con.db.status: # if it was not a connection problem
raise # then propagate the error
else: # otherwise
self.reset() # reset the connection
r = method(*args, **kwargs) # and try one more time
self._usage += 1
return r
return tough_method
def __getattr__(self, name):
"""Inherit the members of the standard connection class.
Some methods are made "tougher" than in the standard version.
"""
attr = getattr(self._con, name)
if name in ('query', 'get', 'insert', 'update', 'delete') \
or name.startswith('get_'):
attr = self._get_tough_method(attr)
return attr
insert = {
"act_id_user": "******",
"act_id_group": "INSERT INTO act_id_group SELECT newvals.id_, newvals.rev_, newvals.name_, newvals.type_ FROM newvals LEFT OUTER JOIN act_id_group ON (act_id_group.id_ = newvals.id_) WHERE act_id_group.id_ IS NULL",
"act_id_membership": "INSERT INTO act_id_membership SELECT newvals.user_id_, newvals.group_id_ FROM newvals LEFT OUTER JOIN act_id_membership ON (act_id_membership.user_id_ = newvals.user_id_) AND (act_id_membership.group_id_ = newvals.group_id_) WHERE act_id_membership.user_id_ IS NULL",
}
src = cfg[args.src]
print("")
print("------------------------------------------------------------------------------------")
print("Source: " + str(args.src))
print("------------------------------------------------------------------------------------")
srcdb = DB(dbname=src["db"], host=src["host"], port=int(src["port"]), user=src["user"], passwd=src["password"])
for srv in args.dst:
item = cfg[srv]
print("")
print("------------------------------------------------------------------------------------")
print("Destination: " + str(srv))
print("------------------------------------------------------------------------------------")
dstdb = DB(dbname=item["db"], host=item["host"], port=int(item["port"]), user=item["user"], passwd=item["password"])
for table in tables:
dstdb.start()
rows = srcdb.query('SELECT * FROM %s' % table).getresult()
dstdb.query('CREATE TEMPORARY TABLE newvals ON COMMIT DROP AS TABLE %s WITH NO DATA' % table)
dstdb.inserttable('newvals', rows)
dstdb.query('LOCK TABLE %s IN EXCLUSIVE MODE' % table)
def connect(self, dbname, host, port, user):
self.db = DB(dbname=dbname, host=host, port=port, user=user)
# -*- coding: utf-8 -*-
import json
import pymongo
from pg import DB
#python 3
pgcnn=DB(dbname='sggis',host='localhost',port=5433,user='******',passwd='123456')
mgcnn=pymongo.MongoClient('192.168.111.250',27017)
cndata=mgcnn.jilin_baishan.data
#cndata.remove({'device_table':'public.road_label_layer'})
def genSql(tbname):
global pgcnn
sql = "select column_name,data_type from information_schema.columns where table_schema='public' and table_name = '%s'" % tbname
columns = pgcnn.query(sql).namedresult()
cols='select row_to_json(tb) as json from (select '
for tup in columns:
name = tup.column_name
if(name!='zscale'):
if(name == 'geometry'):
name = 'st_asgeojson(geometry) as geometry'
elif(name == 'wkb_geometry'):
name = 'st_asgeojson(wkb_geometry)as geometry'
cols+=name+','
cols = cols.rstrip(',')
cols += ' from %s ) tb;' % tbname
return cols
#pgtbs=pgcnn.query("select tablename from pg_tables where schemaname='public' and tablename like 'bs_%_layer' order by tablename;")
#tbrows=pgtbs.namedresult()
tbrows=['pipe']
# -*- coding: utf-8 -*-
"""
insert data to PostgreSQL
@author: Shih-Chi
"""
from pg import DB
import numpy as np
import datetime
db = DB(dbname='Pingtung', host='localhost', port=5432,
user='******', passwd='husky')
import os
for filename in os.listdir('Pingtung/Well'):
site = filename[0:8]
year = filename[9:13]
print(filename)
sites = db.query('select site, starttime, endtime from "Well_Stations" where site like \'{site}\''.format(site=site)).getresult()
if sites != []:
for line in open('Pingtung/Well/{FN}'.format(FN=filename), 'r'):
#檢查時間是否相符
data = line.split()
if len(data[1]) == 8:
if (data[0][0:4] != year) and (data[0][5:10] != '01-01'):
print('檔案有誤 日期不對')
break
else:
print('檔案有誤 時間不對')
break
def main():
db = DB(dbname='databasename', host='127.0.0.1', port=5432, user='******', passwd='...')
for tablename in ('table1', 'table2'):
q = db.query('select * from %s where ...' % tablename)
d = q.dictresult()
json.dump(d, open("%s.txt" % tablename,'w'))
self.country = 0
def handle_endtag(self, tag):
if tag == "th":
self.start = 0
def handle_data(self, data):
if self.start and data == "Country:":
self.country = 1
elif self.found :
self.data.append(data)
self.found = 0
if __name__ == '__main__':
db = DB(dbname='honeypotSSH', host='localhost', user='******', passwd='1')
while True:
try:
ip = DB.query(db, "SELECT ip from connections where ip <>'' and country is NULL LIMIT 1").getresult()[0][0]
print ip
except Exception:
exit(1)
try:
country = DB.query(db, "SELECT country from connections where ip = '" + ip + "' and country is not NULL LIMIT 1").getresult()[0][0]
except IndexError:
country = 'Not Found'
print "Local: " + country
if ip[:8] in '192.168.' or ip[:3] in '10.':
country = 'Private'
print "Private"
if ip in '127.0.0.1':
country = 'Loopback'
print "Loopback"
if country is 'Not Found':
"../old-hp-data/log20110713.txt",
"../old-hp-data/log20110728.txt",
"../old-hp-data/log20110801.txt",
"../old-hp-data/log20110816.txt",
"../old-hp-data/log20110825.txt",
"../old-hp-data/log20110903.txt",
"../old-hp-data/log20110913.txt",
"../old-hp-data/log20111014.txt",
"../old-hp-data/log20111024.txt",
"../old-hp-data/log20111106.txt",
"../old-hp-data/log20111128.txt",
]
honeypotName = 'HoneypotOld'
port = 22
sessionDatabaseID = DB.query(db, "SELECT MAX(session) FROM connections WHERE honeypot='" + honeypotName + "'").getresult()[0][0]
if not sessionDatabaseID:
sessionDatabaseID = 1;
sessionOld = -1
ip = ''
sessionStartTime = ''
sessionEndTime = ''
user = ''
password = ''
for fileName in fileNames:
for line in input(fileName):
if len(line) < 20:
continue
if "Session" in line:
# -*- coding: utf-8 -*-
"""
insert data to PostgreSQL
@author: Shih-Chi
"""
from pg import DB
import numpy as np
import datetime
db = DB(dbname='Pingtung', host='localhost', port=5432,
user='******', passwd='husky')
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'中正(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'石化(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'林園(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'潮寮(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'永芳(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'昭明(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'溪埔(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'九曲(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'大樹(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'旗山(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'美濃(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'旗美(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'新威(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'清溪(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'海豐(1)\'');
db.query('UPDATE "Well_Stations" SET layer = 1 WHERE name like \'前進\'');
import os
from pg import DB
db=DB(dbname='db_meta',host='172.16.1.10',port=5432,user='******',passwd='password')
# 1.drop test tmp tables
q=db.query("SELECT sys_tablename AS tablename FROM sys_component where sys_tablename like 't\_%' order by sys_tablename;")
rows=q.namedresult()
i=0
for row in rows:
tbname=row.tablename
cmd='pg_dump -h 172.16.1.10 -U postgres -F p -n public -s -t %s dbName >> D:/pgdata/db_meta_struct.sql' % tbname;
os.system(cmd)
i=i+1
print('export table: %s ,%d' % (tbname,i))
# -*- coding: utf-8 -*-
#import convexHull as Convex
import json
from pg import DB
import matplotlib.pyplot as plt
from scipy.spatial import ConvexHull as Convex
pgcnn=DB(dbname='fzgis',host='localhost',port=5432,user='******',passwd='123456')
def drawHull(pts,idxs):
x=[]
y=[]
for i in idxs:
x.append(pts[i][0])
y.append(pts[i][1])
x.append(x[0])
y.append(y[0])
plt.plot(x,y,color='red')
plt.show()
fo = open("test.txt", "w")
def wo(item_id,pts,idx):
global fo
coord = []
for i in idx:
coord.append(pts[i])
coord.append(coord[0])
print item_id,len(coord),coord
fo.write('%s@%s\n'%(item_id,coord))
