Esempio n. 1
0
    def testBytesPassword(self):
        db = pg8000.connect(**db_connect)
        # Create user
        username = '******'
        password = u('cha\uFF6Fs')
        cur = db.cursor()

        # Delete user if left over from previous run
        try:
            cur.execute("drop role " + username)
        except pg8000.ProgrammingError:
            cur.execute("rollback")

        cur.execute(
            "create user " + username + " with password '" + password + "';")
        cur.execute('commit;')
        db.close()

        data = db_connect.copy()
        data['user'] = username
        data['password'] = password.encode('utf8')
        data['database'] = 'pg8000_md5'
        if PY2:
            self.assertRaises(
                pg8000.ProgrammingError, pg8000.connect, **data)
        else:
            self.assertRaisesRegex(
                pg8000.ProgrammingError, '3D000', pg8000.connect, **data)

        db = pg8000.connect(**db_connect)
        cur = db.cursor()
        cur.execute("drop role " + username)
        cur.execute("commit;")
        db.close()
Esempio n. 2
0
    def get_connection(self, key, host, port, user, password, dbname, use_cached=True):
        "Get and memoize connections to instances"
        if key in self.dbs and use_cached:
            return self.dbs[key]

        elif host != "" and user != "":
            try:
                service_check_tags = [
                    "host:%s" % host,
                    "port:%s" % port
                ]
                if dbname:
                    service_check_tags.append("db:%s" % dbname)

                if host == 'localhost' and password == '':
                    # Use ident method
                    connection = pg.connect("user=%s dbname=%s" % (user, dbname))
                elif port != '':
                    connection = pg.connect(host=host, port=port, user=user,
                        password=password, database=dbname)
                else:
                    connection = pg.connect(host=host, user=user, password=password,
                        database=dbname)
                status = AgentCheck.OK
                self.service_check('postgres.can_connect', status, tags=service_check_tags)
                self.log.info('pg status: %s' % status)

            except Exception, e:
                status = AgentCheck.CRITICAL
                self.service_check('postgres.can_connect', status, tags=service_check_tags)
                self.log.info('pg status: %s' % status)
                raise
Esempio n. 3
0
def testSocketMissing():
    conn_params = {
        'unix_sock': "/file-does-not-exist",
        'user': "******"}

    with pytest.raises(pg8000.InterfaceError):
        pg8000.connect(**conn_params)
Esempio n. 4
0
    def get_connection(self, key, host, port, user, password, dbname, ssl, use_cached=True):
        "Get and memoize connections to instances"
        if key in self.dbs and use_cached:
            return self.dbs[key]

        elif host != "" and user != "":
            try:
                if host == 'localhost' and password == '':
                    # Use ident method
                    connection = pg.connect("user=%s dbname=%s" % (user, dbname))
                elif port != '':
                    connection = pg.connect(host=host, port=port, user=user,
                        password=password, database=dbname, ssl=ssl)
                else:
                    connection = pg.connect(host=host, user=user, password=password,
                        database=dbname, ssl=ssl)
            except Exception as e:
                message = u'Error establishing postgres connection: %s' % (str(e))
                service_check_tags = self._get_service_check_tags(host, port, dbname)
                self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.CRITICAL,
                    tags=service_check_tags, message=message)
                raise
        else:
            if not host:
                raise CheckException("Please specify a Postgres host to connect to.")
            elif not user:
                raise CheckException("Please specify a user to connect to Postgres as.")

        self.dbs[key] = connection
        return connection
Esempio n. 5
0
def testGss(db_kwargs):
    db_kwargs["database"] = "pg8000_gss"

    # Should raise an exception saying gss isn't supported
    with pytest.raises(
            pg8000.InterfaceError,
            match="Authentication method 7 not supported by pg8000."):
        pg8000.connect(**db_kwargs)
Esempio n. 6
0
    def testBrokenPipe(self):
        db1 = pg8000.connect(**db_connect)
        db2 = pg8000.connect(**db_connect)

        cur1 = db1.cursor()
        cur2 = db2.cursor()

        cur1.execute("select pg_backend_pid()")
        pid1 = cur1.fetchone()[0]

        cur2.execute("select pg_terminate_backend(%s)", (pid1,))
        self.assertRaises(pg8000.OperationalError, cur1.execute, "select 1")
        cur2.close()
        db2.close()
Esempio n. 7
0
def postgres_probably_connect(name, username, host):
    """
    Connect to postgres or die trying.
    """
    import pg8000
    module = pg8000
    if host:
        con = pg8000.connect(host=host, user=username, database=name)
    else:
        con = pg8000.connect(
            username,
            unix_sock='/var/run/postgresql/.s.PGSQL.5432',
            database=name)

    return module, con
def import_json(json_file):
    if os.environ.get("LIFELOG_DB_USER") is None or \
                    os.environ.get("LIFELOG_DB_PASS") is None or \
                    os.environ.get("LIFELOG_DB_HOST") is None or \
                    os.environ.get("LIFELOG_DB_NAME") is None:
        print("Environment variables are not set!")
        sys.exit(1)

    print("Connecting...")
    json_data = json.load(open(json_file))
    conn = pg8000.connect(user=os.environ.get("LIFELOG_DB_USER"),
                          password=os.environ.get("LIFELOG_DB_PASS"),
                          host=os.environ.get("LIFELOG_DB_HOST"),
                          database=os.environ.get("LIFELOG_DB_NAME"))
    cursor = conn.cursor()

    print("Rebuilding tables...")
    sql = open("database.sql", "r").read()
    cursor.execute(sql)
    conn.commit()

    print("Inserting data...")

    for item in json_data:
        row = [item["name"], item["data"]]
        cursor.execute('INSERT INTO images (name, data) VALUES (%s, %s)', row)
    conn.commit()
    cursor.close()
def db_connect():
    return pg8000.connect(
        host="host",
        user="******",
        password="******",
        database="database"
    )
Esempio n. 10
0
def import_topics(topics_file, qrels_file):
    if os.environ.get("LIFELOG_DB_USER") is None or \
                    os.environ.get("LIFELOG_DB_PASS") is None or \
                    os.environ.get("LIFELOG_DB_HOST") is None or \
                    os.environ.get("LIFELOG_DB_NAME") is None:
        print("Environment variables are not set!")
        sys.exit(1)

    topics = []
    topics_dom = et.parse(topics_file)
    for topic_el in topics_dom.iter('topic'):
        id, title, description, narrative = '', '' ,'' ,''
        for node in topic_el:
            if node.tag == 'id':
                id = node.text
            elif node.tag == 'title':
                title = node.text
            elif node.tag == 'description':
                description = node.text
            elif node.tag == 'narrative':
                narrative = node.text
        topics.append(Topic(id, title, description, narrative))

    print(topics)

    with open(qrels_file, 'r') as f:
        qrels = f.read().split('\n')

    images = {}
    for line in qrels:
        topic_id, _, image_path, relevance = line.split(' ')
        if relevance == '1':
            image = image_path.split('/')[-1].replace('.jpg', '')
            images[image] = topic_id

    conn = pg8000.connect(user=os.environ.get("LIFELOG_DB_USER"),
                          password=os.environ.get("LIFELOG_DB_PASS"),
                          host=os.environ.get("LIFELOG_DB_HOST"),
                          database=os.environ.get("LIFELOG_DB_NAME"))
    cursor = conn.cursor()

    with progressbar.ProgressBar(max_value=len(topics)) as bar:
        i = 0
        for topic in topics:
            cursor.execute('INSERT INTO topics (topic_id, title, description, narrative) '
                           'VALUES (%s, %s, %s, %s)',
                           [topic.id, topic.title, topic.description, topic.narrative])
            i += 1
            bar.update(i)

    with progressbar.ProgressBar(max_value=len(images)) as bar:
        i = 0
        for image_id, topic_id in images.items():
            cursor.execute('INSERT INTO images_topics (image_id, topic_id) '
                           'VALUES (%s, %s)',
                           [image_id, topic_id])
            i += 1
            bar.update(i)
    conn.commit()
    cursor.close()
def get_data(_type, _sort):
    conn = pg8000.connect(user="******", database="mondial")
    cursor = conn.cursor()

    sort_result = ''
    if _sort == 'name':
        sort_result = 'ORDER BY name'
    elif _sort in ('area', 'elevation'):
        sort_result = 'ORDER BY ' + _sort + ' DESC'

    if _type:
        sql = "SELECT name, elevation, area, type FROM lake WHERE type = %s " + sort_result
        cursor.execute(sql, [str(_type)])
    else:
        sql = "SELECT name, elevation, area, type FROM lake " + sort_result
        cursor.execute(sql)

    # nested function
    def get_int(value):
        if isinstance(value, decimal.Decimal):
            return int(value)
        else:
            return None

    query_result = []
    for row in cursor.fetchall():
        item = {
            "name": row[0],
            "elevation": get_int(row[1]),
            "area": get_int(row[2]),
            "type": row[3]
        }
        query_result.append(item)

    return query_result
Esempio n. 12
0
 def __init__(self, user, password, database, host):
     """Connect to database"""
     self.__db = pg8000.connect(user=user,
                                password=password,
                                database=database,
                                host=host)
     self.__cursor = self.__db.cursor()
 def handler(pgsql):
     conn = pg8000.connect(**pgsql.dsn())
     with closing(conn.cursor()) as cursor:
         cursor.execute("CREATE TABLE hello(id int, value varchar(256))")
         cursor.execute("INSERT INTO hello values(1, 'hello'), (2, 'ciao')")
     conn.commit()
     conn.close()
 def create_default_database(self):
     with closing(pg8000.connect(**self.dsn(database='postgres'))) as conn:
         conn.autocommit = True
         with closing(conn.cursor()) as cursor:
             cursor.execute("SELECT COUNT(*) FROM pg_database WHERE datname='test'")
             if cursor.fetchone()[0] <= 0:
                 cursor.execute('CREATE DATABASE test')
Esempio n. 15
0
def sync():
	tids = request.args.getlist('tid')
	if len(tids) == 0:
		return json.dumps({})
	try:
		conn = pg8000.connect(user="******", password="******", database="cb15rna", host="cb15rna.ciacashmbpf0.us-east-1.rds.amazonaws.com")
		cur = conn.cursor()
		cur.execute("select time, tid, tpm*1000000, attempt from differential where tid in ("+','.join("'"+tid+"'" for tid in tids)+") order by tid,attempt,time")
		data = cur.fetchall()
		cur.close()
		conn.close()
		data_dict = {}
		for d in data:
 			d[0] = str(d[0])
 			if d[3] not in data_dict.keys():
				data_dict[d[3]] = {}
			if d[1] not in data_dict[d[3]].keys():
				data_dict[d[3]][d[1]] = [[d[0],d[2]]]  
			else:
				data_dict[d[3]][d[1]].append([d[0],d[2]])
		# print json.dumps(data_dict)
		return json.dumps(data_dict)
	except:  
		traceback.print_exc()
	return json.dumps(tids)
Esempio n. 16
0
def get_table_data(exp_name, start, length, direction, col, query):
	try:
		conn = pg8000.connect(user="******", password="******", database="cb15rna", host="cb15rna.ciacashmbpf0.us-east-1.rds.amazonaws.com")
		
		cur = conn.cursor()
		qstr = construct_query(exp_name, start, length, direction, col)
		format_qstr = '%'+query+'%'
		cur.execute(qstr, (format_qstr,))
		data = cur.fetchall()
		cur.close()

		cur = conn.cursor()
		cur.execute('select count(*) from '+exp_name)
		count = cur.fetchall()
		cur.close()

		cur = conn.cursor()
		cur.execute('select count(*) from '+exp_name+' where tid ilike %s',(format_qstr,))
		filteredCount = cur.fetchall()
		cur.close()
		
		conn.close()
		dataList = list(data)
		return {'recordsTotal':count[0][0],'recordsFiltered':filteredCount[0][0],'data':dataList}
	except:  
		traceback.print_exc()
Esempio n. 17
0
def execute(sqls):
    connection = pg8000.connect(host=conf.HOST, user='******', password='******', database='dblp')
    cursor = connection.cursor()
    for sql in sqls:
        cursor.execute(sql.replace('%', '%%'))
    connection.commit()
    log.debug("Done processing.")
def main():
    pg8000.paramstyle   = "qmark"
    conn1               = sqlite3.connect('../data/dumps_new.db')
    conn2               = pg8000.connect(user="******", host="npaa4726", port=5432, database="cprodev", password="******") 
    insert_cursor       = conn2.cursor()
    select_cursor   = conn1.execute("""
        SELECT  DATE
        ,       TIME
        ,       USER
        ,       ERROR
        ,       PROGRAM
        ,       SERVER
        ,       REQUEST
        ,       REQUEST_USER
        ,       REQUEST_DATE
        ,       REQUEST_TIME
        ,       REQUEST_DESCRIPTION
        FROM    SAP_DUMPS_NEW
        WHERE   DATE >= '2018-05-01'
        ORDER   BY DATE
        ,       TIME
    """)
    count = 0
    for row in select_cursor:
        insert_cursor.execute("""
        INSERT INTO SAP_DUMPS(
                DATE
        ,       TIME
        ,       USER_
        ,       ERROR
        ,       PROGRAM
        ,       SERVER
        ,       REQUEST
        ,       REQUEST_USER
        ,       REQUEST_DATE
        ,       REQUEST_TIME
        ,       REQUEST_DESCRIPTION
        )   VALUES (
                ? -- DATE
        ,       ? -- TIME
        ,       ? -- USER
        ,       ? -- ERROR
        ,       ? -- PROGRAM
        ,       ? -- SERVER
        ,       ? -- REQUEST
        ,       ? -- REQUEST_USER
        ,       ? -- REQUEST_DATE
        ,       ? -- REQUEST_TIME
        ,       ? -- REQUEST_DESCRIPTION        
        )
        """, args=row)
        count += 1
        if count % 1000 == 0:
            print()
            print(count)
            print()
        else:
            print(".", end="")
    conn2.commit()
Esempio n. 19
0
 def is_connection_available(self):
     try:
         with closing(pg8000.connect(**self.dsn(database='template1'))):
             pass
     except pg8000.Error:
         return False
     else:
         return True
    def _get_connection(self, host, port, user, password, dbname):

        if host != "" and user != "":
            if host == "localhost" and password == "":
                # Use ident method
                connection = pg.connect("user=%s dbname=%s" % (user, dbname))
            elif port != "":
                connection = pg.connect(host=host, port=port, user=user, password=password, database=dbname, ssl=False)
            else:
                connection = pg.connect(host=host, user=user, password=password, database=dbname, ssl=ssl)
        else:
            if not host:
                raise CheckException("Please specify a Postgres host to connect to.")
            elif not user:
                raise CheckException("Please specify a user to connect to Postgres as.")

        return connection
Esempio n. 21
0
 def connect(self):
     self.connection = pg8000.connect(
             host=self.host, port=self.port, database=self.database, user=self.user, password=self.password)
     self.connection.autocommit = False
     self.cursor = self.connection.cursor()
     self.cursor.execute("set max_parallel_degree = 64;")
     self.cursor.execute("set temp_tablespaces = temp_tbs;")
     self.cursor.execute("select pg_reload_conf();")
Esempio n. 22
0
 def _is_connection_available(self):
     """Tests if the connection to the new cluster is available
     """
     try:
         with closing(pg8000.connect(**self.dsn)):
             return True
     except pg8000.Error:
         return False
Esempio n. 23
0
 def testSsl(self):
     data = db_connect.copy()
     data["ssl"] = True
     if PRE_26:
         self.assertRaises(pg8000.InterfaceError, pg8000.connect, **data)
     else:
         db = pg8000.connect(**data)
         db.close()
Esempio n. 24
0
def testBytesPassword(con, db_kwargs):
    # Create user
    username = '******'
    password = '******'
    with con.cursor() as cur:
        cur.execute(
            "create user " + username + " with password '" + password + "';")
        con.commit()

        db_kwargs['user'] = username
        db_kwargs['password'] = password.encode('utf8')
        db_kwargs['database'] = 'pg8000_md5'
        with pytest.raises(pg8000.ProgrammingError, match='3D000'):
            pg8000.connect(**db_kwargs)

        cur.execute("drop role " + username)
        con.commit()
def get_pg_conn(db_host, db, db_user, db_pwd, schema_name, db_port=5439, query_group=None, query_slot_count=1,
                ssl=True, **kwargs):
    conn = None

    if debug:
        comment('Connect %s:%s:%s:%s' % (db_host, db_port, db, db_user))

    try:
        conn = pg8000.connect(user=db_user, host=db_host, port=int(db_port), database=db, password=db_pwd,
                              ssl=ssl, timeout=None)
        conn._usock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
        conn.autocommit = True
    except Exception as e:
        print("Exception on Connect to Cluster: %s" % e)
        print('Unable to connect to Cluster Endpoint')
        cleanup(conn)

        return None

    # set search paths
    aws_utils.set_search_paths(conn, schema_name, exclude_external_schemas=True)

    if query_group is not None and query_group != '':
        set_query_group = 'set query_group to %s' % query_group

        if debug:
            comment(set_query_group)

        run_commands(conn, [set_query_group])

    set_slot_count = None
    if query_slot_count is not None and query_slot_count > 1:
        set_slot_count = 'set wlm_query_slot_count = %s' % query_slot_count

    if set_slot_count is not None:
        if debug:
            comment(set_slot_count)
        run_commands(conn, [set_slot_count])

    # set a long statement timeout
    set_timeout = "set statement_timeout = '36000000'"

    if debug:
        comment(set_timeout)

    run_commands(conn, [set_timeout])

    # set application name
    set_name = "set application_name to 'AnalyzeVacuumUtility-v%s'" % __version__

    if debug:
        comment(set_name)

    run_commands(conn, [set_name])

    comment("Connected to %s:%s:%s as %s" % (db_host, db_port, db, db_user))

    return conn
Esempio n. 26
0
def getPgConn(jdbc_file_path = 'data/jdbc.properties'):
  import pg8000
  
  prop = readProperties(jdbc_file_path)
  conn = pg8000.connect(
            user=prop['jdbc.user'], password=prop['jdbc.password'],
            host=prop['jdbc.host'], port=int(prop['jdbc.port']),
            database=prop['jdbc.dbname'])
  return conn
Esempio n. 27
0
 def make(klass):
     import pg8000
     import sustentacao.conf as conf
     import sustentacao.security.repository as ssr
     pg8000.paramstyle       = "qmark"
     dbcon                   = pg8000.connect(**conf.SECURITY_CONFIG["pg_settings"])
     repo        = ssr.DbSecurityRepo(dbcon)
     controller  = klass(repo)
     return controller
Esempio n. 28
0
 def connect_db():
     return pg8000.connect(
             database=os.environ['DATABASE'],
             host=os.environ['HOST'],
             port=int(os.environ['APP_PORT']),
             user=os.environ['USER'],
             password=os.environ['PASSWORD'],
             ssl=True
             )
Esempio n. 29
0
 def make(klass):
     import pg8000
     pg8000.paramstyle       = "qmark"
     dbcon                   = pg8000.connect(**conf.JOBS_CONFIG["pg_settings"])
     interactor              = sp.Interactor.make()
     jobs_repo               = sjr.JobsRepo(dbcon)
     report_renderer         = ReportRenderer(interactor)
     jobs_controller         = klass(jobs_repo, report_renderer)
     return jobs_controller
Esempio n. 30
0
 def connect_to_postgres():
     try:
         return connect(host=host, user=u"postgres", port=port,
                        database=database)
     except (InterfaceError, ProgrammingError) as e:
         Message.new(
             message_type=u"acceptance:integration:postgres_connect",
             exception=unicode(e.__class__), reason=unicode(e)).write()
         return False
Esempio n. 31
0
def main():
    """
    Main function
    """

    parser = argparse.ArgumentParser(
        description=""" Export FloodFront marker data into CSV format. """)
    parser.add_argument(
        '--since',
        type=str,
        help=""" Narrow selection to markers after this date. YYYY-MM-DD """)
    parser.add_argument('-o',
                        '--output',
                        type=str,
                        help=""" File output name. """)

    args = parser.parse_args()
    if (args.since is not None) and (re.search("^[0-9]{4}-[0-9]{2}-[0-9]{2}$",
                                               args.since) is None):
        raise ValueError(
            "Invalid date entered {0}. Date must be YYYY-MM-DD".format(
                args.since))

    engine = settings.DATABASES['default']['ENGINE']
    if engine == 'django.db.backends.sqlite3':
        sqlite_db_file = os.path.join(config['floodfront']['ProjectPath'],
                                      'db.sqlite3')
        conn = sqlite.connect(sqlite_db_file)
        tty_print('Using SQLite engine.')
    elif engine == 'django.db.backdends.postgresql':
        conn = pg.connect(user="******", database="floodfront")
        tty_print('Using Postgres engine.')
    else:
        sys.stderr.write('Engine not supported: {0}'.format(engine))
        exit(1)
    cursor = conn.cursor()
    query = """ SELECT id, email, lat, lon, accuracy, created_on, marker_type, description
            FROM server_marker """

    if args.since is not None:
        tty_print("Searching for date {0}".format(args.since))
        query = query + "WHERE created_on >= '{0}'".format(args.since)
    else:
        now = strftime("%Y-%m-%d", gmtime())
        tty_print("Searching for date {0} (default today)".format(now))
        query = query + "WHERE created_on >= '{0}'".format(now)

    cursor.execute(query)
    result = cursor.fetchall()

    result = sorted(result, key=lambda row: row[5], reverse=True)

    filename = "marker.csv"
    if args.output is not None:
        filename = args.output

    if sys.stdout.isatty():
        with open(filename, 'w') as csvfile:
            writer = csv.writer(csvfile, dialect='excel')
            for row in result:
                if isinstance(row[5], datetime.datetime):
                    created_on = row[5].strftime("%Y-%m-%dT%H:%M:%S")
                elif isinstance(row[5], str):
                    created_on = str(
                        datetime.datetime.strptime(row[5],
                                                   '%Y-%m-%d %H:%M:%S.%f'))
                writer.writerow([
                    row[0], row[1],
                    round(float(row[2]), 6),
                    round(float(row[3]), 6),
                    round(float(row[4]), 6), created_on,
                    type_to_class(row[6]), (str(row[7] or "No description."))
                ])
    else:
        # Pipe mode
        writer = csv.writer(sys.stdout, dialect='excel')
        for row in result:
            if isinstance(row[5], datetime.datetime):
                created_on = row[5].strftime("%Y-%m-%dT%H:%M:%S")
            elif isinstance(row[5], str):
                created_on = str(
                    datetime.datetime.strptime(row[5], '%Y-%m-%d %H:%M:%S.%f'))
            writer.writerow([
                row[0], row[1],
                round(float(row[2]), 6),
                round(float(row[3]), 6),
                round(float(row[4]), 6), created_on,
                type_to_class(row[6]), (str(row[7] or "No description."))
            ])
def handler(event, context):

    start_time = time.time()

    # open database connection
    pg = pg8000.connect(host=os.environ['database_host'],
                        port=5432,
                        database=os.environ['database_name'],
                        user=os.environ['database_user'],
                        password=os.environ['database_password'])

    # get clans
    pg_cursor = pg.cursor()
    pg_cursor.execute(
        "SELECT clan_id, clan_name FROM groups.t_clans WHERE clan_name NOT IN ('Iron Orange 3rd Bn', 'Iron Orange Moon') ORDER BY clan_name"
    )
    clans = pg_cursor.fetchall()

    # close database connection
    pg.close()

    # get active members
    active = {}

    request_details = []

    for clan in clans:
        url = os.environ['clan_url'].format(clan[0])
        #print(url) # debugging
        clan.append(url)
        request_details.append(clan)

    responses = process_requests(request_details)

    active_members = []

    for response in responses:
        for member in response[3]:
            if 'isOnline' in member.keys():
                if member['isOnline']:
                    #print(member['destinyUserInfo']['displayName']) # debugging
                    active_member = {}
                    active_member['clan_name'] = response[1]
                    active_member['gamertag'] = member['destinyUserInfo'][
                        'displayName']
                    active_member['destiny'] = member['destinyUserInfo']
                    active_member['profile_url'] = os.environ[
                        'character_url'].format(
                            member['destinyUserInfo']['membershipType'],
                            member['destinyUserInfo']['membershipId'])

                    active_members.append(active_member)

    #print(active_members) # debugging

    character_activities = process_profile_requests(active_members)
    #print(json.dumps(character_activities))
    print('Sorting character activities')
    character_activities = sorted(character_activities,
                                  key=itemgetter('clan_name'))

    actives = {}
    for gamertag in character_activities:
        if len(gamertag['activity_detail']) > 0:
            combined = '{0}: {1}'.format(gamertag['gamertag'],
                                         gamertag['activity_detail'])
        else:
            combined = gamertag['gamertag']

        if gamertag['clan_name'] not in actives:
            actives[gamertag['clan_name']] = []

        actives[gamertag['clan_name']].append(combined)

    active_members = {}

    print(json.dumps(actives))
    for clan in actives:
        sorted_actives = sorted(actives[clan], key=str.lower)
        single_string = '\n'.join(sorted_active
                                  for sorted_active in sorted_actives)

        active_members[clan] = {}
        active_members[clan] = single_string

    #print(json.dumps(active_members)) # debugging

    duration = time.time() - start_time
    print('Duration: {0:.2f}s'.format(duration))

    r.hmset('online', {"json": json.dumps(active_members)})

    return active_members
Esempio n. 33
0
 def setUp(self):
     self.db = pg8000.connect(**db_connect)
     self.cursor = self.db.cursor()
Esempio n. 34
0
def test_parameterized_record_query(pg_params, queries):
    with db.connect(**pg_params, row_factory=dict_row) as conn:
        t.run_parameterized_record_query(conn, queries, "pg", date)
Esempio n. 35
0
import pg8000 as pg

city = "tucson"
conn = pg.connect(user='******',
                  password='******',
                  host='postgresql.crvadswmow49.us-west-2.rds.amazonaws.com',
                  database='Networkland')  # port default 5432
cursor = conn.cursor()
query = "SELECT gid, ST_StartPoint(geom), ST_EndPoint(geom), ST_Length(geom) FROM {}".format(
    city)
cursor.execute(query)
results = cursor.fetchall()
print 'results fetched'

nodes_gid = {}  # (fromnode, tonode) : gid
nodes_length = {}  # (fromnode, tonode) : length
for row in results:
    if (row[1], row[2]) not in nodes_gid:
        nodes_gid[(row[1], row[2])] = row[0]
        nodes_length[(row[1], row[2])] = row[3]
    else:
        duplicate = nodes_gid[(row[1], row[2])]
        print "ERROR: {} HAS DUPLICATE NODES with {}".format(row[0], duplicate)
Esempio n. 36
0
 def testSsl(self):
     data = db_connect.copy()
     data["ssl"] = True
     db = pg8000.connect(**data)
     db.close()
def main():
    module = AnsibleModule(argument_spec=dict(
        login_user=dict(default="rs_master"),
        login_password=dict(default="", no_log=True),
        login_host=dict(default=""),
        login_unix_socket=dict(default=""),
        login_ssl=dict(default=True, type='bool'),
        port=dict(default=5439, type='int'),
        db=dict(required=True),
        user=dict(default=''),
        password=dict(default=None, no_log=True),
        update_password=dict(default="always", choices=["always",
                                                        "on_create"]),
        group=dict(default=''),
        state=dict(default="present", choices=["absent", "present"]),
        permission_flags=dict(default=[], type='list'),
        privs=dict(default=[], type='list'),
        expires=dict(default=None),
        conn_limit=dict(default=None)),
                           supports_check_mode=True)

    user = module.params["user"]
    password = module.params["password"]
    update_password = module.params["update_password"]
    state = module.params["state"]
    db = module.params["db"]
    group = module.params["group"]
    port = module.params["port"]
    permission_flags = module.params["permission_flags"]
    privs = module.params["privs"]
    expires = module.params["expires"]
    conn_limit = module.params["conn_limit"]

    if not pg8000_found:
        module.fail_json(msg="the python pg8000 module is required")

    # To use defaults values, keyword arguments must be absent, so
    # check which values are empty and don't include in the **kw
    # dictionary
    params_map = {
        "login_host": "host",
        "login_user": "******",
        "login_password": "******",
        "login_ssl": "ssl",
        "port": "port",
        "db": "database"
    }
    kw = dict((params_map[k], v) for (k, v) in iteritems(module.params)
              if k in params_map and v != "")

    # If a login_unix_socket is specified, incorporate it here.
    is_localhost = "host" not in kw or kw["host"] == "" or kw[
        "host"] == "localhost"
    if is_localhost and module.params["login_unix_socket"] != "":
        kw["host"] = module.params["login_unix_socket"]

    cursor = None
    try:
        pg8000.paramstyle = "pyformat"
        db_connection = pg8000.connect(**kw)
        db_connection.autocommit = False
        cursor = db_connection.cursor()
    except InterfaceError:
        e = get_exception()
        module.fail_json(
            msg=
            "unable to connect to database, check credentials and SSL flag!: %s "
            % e)
    except Exception:
        e = get_exception()
        module.fail_json(msg="unable to connect to database: %s" % e)

    kw = {'user': user, 'group': group}
    changed = False
    user_added = False
    group_added = False
    user_removed = False
    group_removed = False

    # ===========================================
    # Main decision tree
    #
    try:
        if state == "present":
            if user != '':
                if not user_exists(cursor, user):
                    user_change(cursor, user, password, permission_flags,
                                expires, conn_limit)
                    changed = True
                    user_added = True
                else:
                    current_user_data = get_user(cursor, user)
                    if update_password == "on_create":
                        password = None
                    user_change(cursor, user, password, permission_flags,
                                expires, conn_limit, 'ALTER')
                    updated_user_data = get_user(cursor, user)
                    changed = update_password == "always" or current_user_data != updated_user_data

            if user == '' and group != '' and not group_exists(cursor, group):
                group_add(cursor, group)
                changed = True
                group_added = True

            group_updated = False
            if user != '':
                group_updated = group_assign(cursor, group, user)

            privs_updated = apply_privs(cursor, privs, user, group)

            changed = changed or group_updated or privs_updated

        # absent case
        else:
            if user != '' and user_exists(cursor, user):
                group_assign(cursor, None, user)
                user_delete(cursor, user)
                changed = True
                user_removed = True

            if user == '' and group != '' and group_exists(cursor, group):
                group_delete(cursor, group)
                changed = True
                group_removed = True

    except ValueError:
        e = get_exception()
        module.fail_json(msg="Invalid module input: %s" % e)
    except (InterfaceError, DatabaseError):
        e = get_exception()
        module.fail_json(msg="Database error occured: %s" % e)

    if changed:
        if module.check_mode:
            db_connection.rollback()
        else:
            db_connection.commit()

    cursor.close()

    kw['changed'] = changed
    kw['user_added'] = user_added
    kw['group_added'] = group_added
    kw['user_removed'] = user_removed
    kw['group_removed'] = group_removed
    module.exit_json(**kw)
Esempio n. 38
0
#!/usr/bin/python3
import pprint, time
# Connect to Mongo DB
import pymongo
mongo = pymongo.MongoClient('localhost', 3001)
mongodb = mongo.meteor
mdata = mongodb.markdata

# Connect to Postgresql DB
import pg8000
dbc = pg8000.connect(user="", password="", host="", port=5432, database='')
db = dbc.cursor()

# Make a list of dictionaries with the marketing days with
# "_id", "mark_attempted" and "money_spent"

#     get a list of marketing days from Postgresql
print(
    time.ctime(),
    'Obtaining data from Postgres with  "_id", "mark_attempted" and "money_spent" '
)
db.execute(''' 
                SELECT date(sample_col_1)::text as _id, 
                        count(sample_col_2)::integer as mark_attempted
                FROM mark_attempts
                GROUP BY date(sample_col_1)
                ORDER BY date(sample_col_1) ASC
            ''')
rawMarkDays = db.fetchall()

#    transform the list obtained from Postgresql to a list of dictionaries
Esempio n. 39
0
def test_record_query(pg_params, queries):
    with db.connect(**pg_params, row_factory=dict_row) as conn:
        t.run_record_query(conn, queries)
Esempio n. 40
0
def random_lake_sentence(lakes, sentences):
    lake = random.choice(lakes)
    possible_keys = [k for k in lake.keys() if k != 'name' \
            and lake[k] is not None]
    col = random.choice(possible_keys)
    sentence_template = sentences[col]
    output = sentence_template.format(lake['name'], lake[col])
    return output


twitter = twython.Twython(api_key, api_secret, access_token, token_secret)

lakes = []

conn = pg8000.connect(database="mondial")  # may need extra auth info!
cursor = conn.cursor()
cursor.execute("SELECT name, area, depth, elevation, type, river FROM lake")
for row in cursor.fetchall():
    lake = {
        'name': row[0],
        'area': row[1],
        'depth': row[2],
        'elevation': row[3],
        'type': row[4],
        'river': row[5]
    }
    lakes.append(lake)

sentences = {
    'area': 'The area {} is {} square kilometers.',
Esempio n. 41
0
import pg8000 as pg
import time
import config

# This file is exported from
print '...'
start_time = time.time()

folder = 'D:/Will/Metropia/Network Updates/New York/Update 6-28-2016/'
city = "osm_newyork"
out_boundary = open(folder + city + '_polygon.json', 'wb')

conn = pg.connect(user=config.NETWORKLAND_USER,
                  password=config.NETWORKLAND_PASSWORD,
                  host=config.NETWORKLAND_URL,
                  database=config.NETWORKLAND_DB)  # port default 5432
cursor = conn.cursor()

query = "SELECT ST_AsGeoJSON(geom) FROM {}_polygons WHERE type='boundary'".format(
    'newyork')
cursor.execute(query)
results = cursor.fetchall()
for row in results:
    out_boundary.write(row[0])

print("Elapsed time... %s seconds" % (time.time() - start_time))
def snapshot(config_sources):
    aws_region = get_config_value(['AWS_REGION'], config_sources)

    set_debug = get_config_value([
        'DEBUG',
        'debug',
    ], config_sources)
    if set_debug is not None and set_debug.upper() == 'TRUE':
        global debug
        debug = True

    kms = boto3.client('kms', region_name=aws_region)

    if debug:
        print("Connected to AWS KMS & CloudWatch in %s" % aws_region)

    user = get_config_value(['DbUser', 'db_user', 'dbUser'], config_sources)
    host = get_config_value(
        ['HostName', 'cluster_endpoint', 'dbHost', 'db_host'], config_sources)
    port = int(
        get_config_value(['HostPort', 'db_port', 'dbPort'], config_sources))
    database = get_config_value(['DatabaseName', 'db_name', 'db'],
                                config_sources)

    # we may have been passed the password in the configuration, so extract it if we can
    pwd = get_config_value(['db_pwd'], config_sources)

    if pwd is None:
        enc_password = get_config_value([
            'EncryptedPassword', 'encrypted_password', 'encrypted_pwd',
            'dbPassword'
        ], config_sources)
        # resolve the authorisation context, if there is one, and decrypt the password
        auth_context = get_config_value('kms_auth_context', config_sources)

        if auth_context is not None:
            auth_context = json.loads(auth_context)

        try:
            if auth_context is None:
                pwd = kms.decrypt(
                    CiphertextBlob=base64.b64decode(enc_password))['Plaintext']
            else:
                pwd = kms.decrypt(
                    CiphertextBlob=base64.b64decode(enc_password),
                    EncryptionContext=auth_context)['Plaintext']
        except:
            print('KMS access failed: exception %s' % sys.exc_info()[1])
            print('Encrypted Password: %s' % enc_password)
            print('Encryption Context %s' % auth_context)
            raise

    # Connect to the cluster
    try:
        if debug:
            print('Connecting to Redshift: %s' % host)

        conn = pg8000.connect(database=database,
                              user=user,
                              password=pwd,
                              host=host,
                              port=port,
                              ssl=ssl)
    except:
        print('Redshift Connection Failed: exception %s' % sys.exc_info()[1])
        raise

    if debug:
        print('Successfully Connected to Cluster')

    # create a new cursor for methods to run through
    cursor = conn.cursor()

    # set application name
    set_name = "set application_name to 'RedshiftSystemTablePersistence-v%s'" % __version__

    if debug:
        print(set_name)

    cursor.execute(set_name)

    # load the table configuration
    table_config = json.load(
        open(
            os.path.dirname(__file__) + '/lib/history_table_config.json', 'r'))

    # create the dependent objects if we need to
    create_schema_objects(cursor, conn)

    # snapshot stats into history tables
    insert_rowcounts = snapshot_system_tables(cursor, conn, table_config)

    # cleanup history tables if requested in the configuration
    delete_rowcounts = None
    cleanup_after_days = get_config_value(
        [config_constants.SYSTABLE_CLEANUP_AFTER_DAYS], config_sources)
    if cleanup_after_days is not None:
        try:
            cleanup_after_days = int(cleanup_after_days)
        except ValueError:
            print("Configuration value '%s' must be an integer" %
                  config_constants.SYSTABLE_CLEANUP_AFTER_DAYS)
            raise

        if cleanup_after_days > 0:
            delete_rowcounts = cleanup_snapshots(cursor, conn,
                                                 cleanup_after_days,
                                                 table_config)

        cursor.close()
        conn.close()

    return {"inserted": insert_rowcounts, "deleted": delete_rowcounts}
Esempio n. 43
0
 def __init__(self, *args, **kwargs):
     self.conn = pg8000.connect(*args, **kwargs)
     self.apply_migrations()
Esempio n. 44
0
def main():
    opts = optparse.OptionParser()
    opts.add_option('-e', '--exchange', help='exchange name')
    opts.add_option('-t', '--sec_type', help='sec type: ' + ', '.join(types))
    opts.add_option('-f', '--file', help='security symbol list file')
    opts = opts.parse_args()[0]

    if not opts.exchange:
        print("Error: --exchange not give")
        return

    if not opts.file:
        print("Error: --file not give")
        return

    if not opts.sec_type:
        print("Error: --sec_type not give")
        return

    if opts.sec_type not in types:
        print("Error: invalid sec_type")
        return

    conn = pg8000.connect(host='127.0.0.1',
                          database='opentrade',
                          user='******',
                          password='******')

    cursor = conn.cursor()
    exchanges = {}
    cursor.execute('select name, id from exchange')
    for m in cursor.fetchall():
        exchanges[m[0]] = m[1]
    exchange_id = exchanges.get(opts.exchange)
    if exchange_id is None:
        print('unknown exchange: ' + opts.exchange)
        return
    cursor.execute('select bbgid from security')
    bbgids = set([r[0] for r in cursor.fetchall()])

    with open(opts.file) as fh:
        fh.readline()
        for line in fh:
            toks = line.strip().split(',')
            fields = [
                'symbol', 'local_symbol', '', 'currency', 'bbgid', 'sedol',
                'isin', 'cusip', 'close_price', 'adv20', 'market_cap',
                'sector', 'industry_group', 'industry', 'sub_industry', '',
                'lot_size', 'multiplier', ''
            ]
            values = []
            valid_fields = []
            bbgid = None
            for i in range(0, len(fields)):
                f = fields[i]
                if not f: continue
                valid_fields.append(fields[i])
                values.append(toks[i])
                v = values[-1]
                if not v:
                    values[-1] = None
                    continue
                if f == 'bbgid': bbgid = v
                if f in ('close_price', 'adv20', 'market_cap', 'lot_size',
                         'multiplier'):
                    values[-1] = float(v)
                if f in ('sector', 'industry_group', 'industry',
                         'sub_industry'):
                    values[-1] = int(v)
            values[0] = values[0].split()[0]
            if bbgid not in bbgids:
                valid_fields.append('"type"')
                values.append(opts.sec_type)
                valid_fields.append('exchange_id')
                values.append(exchange_id)
                cursor.execute(
                    'insert into security (' + ', '.join(valid_fields) +
                    ') values(' + ', '.join(['%s'] * len(valid_fields)) + ')',
                    values)
            else:
                cursor.execute(
                    'update security set ' +
                    ', '.join([f + '=%s'
                               for f in valid_fields]) + ' where bbgid=%s',
                    values + [bbgid])
    conn.commit()
Esempio n. 45
0
 def _get_connection(self):
     return pg8000.connect(database=self.database,
                           user=self.user,
                           password=self.password,
                           host=self.host,
                           port=self.port)
def get_pg_conn():
    global db_connections
    pid = str(os.getpid())

    conn = None

    # get the database connection for this PID
    try:
        conn = db_connections[pid]
    except KeyError:
        pass

    if conn == None:
        # connect to the database
        if debug:
            comment('Connect [%s] %s:%s:%s:%s' %
                    (pid, db_host, db_port, db, db_user))

        try:
            conn = pg8000.connect(user=db_user,
                                  host=db_host,
                                  port=db_port,
                                  database=db,
                                  password=db_pwd,
                                  ssl=ssl_option,
                                  timeout=None,
                                  keepalives=1,
                                  keepalives_idle=200,
                                  keepalives_interval=200,
                                  keepalives_count=5)
        except Exception as e:
            write(e)
            write('Unable to connect to Cluster Endpoint')
            cleanup()
            return ERROR

        # set default search path
        search_path = 'set search_path = \'$user\',public,%s' % (
            analyze_schema)
        if target_schema != None and target_schema != analyze_schema:
            search_path = search_path + ', %s' % (target_schema)

        if debug:
            comment(search_path)

        cursor = None
        try:
            cursor = conn.cursor()
            cursor.execute(search_path)
        except pg8000.Error as e:
            if re.match('schema "%s" does not exist' %
                        (analyze_schema, ), e.message) != None:
                write('Schema %s does not exist' % (analyze_schema, ))
            else:
                write(e.message)
            return None

        if query_group != None:
            set_query_group = 'set query_group to %s' % (query_group)

            if debug:
                comment(set_query_group)

            cursor.execute(set_query_group)

        if query_slot_count != None and query_slot_count != 1:
            set_slot_count = 'set wlm_query_slot_count = %s' % (
                query_slot_count)

            if debug:
                comment(set_slot_count)

            cursor.execute(set_slot_count)

        # set a long statement timeout
        set_timeout = "set statement_timeout = '1200000'"
        if debug:
            comment(set_timeout)

        cursor.execute(set_timeout)

        # cache the connection
        db_connections[pid] = conn

    return conn
Esempio n. 47
0
def make_db_connection():
    try:
        return pg8000.connect(**CREDENTIALS)
    except pg8000.DatabaseError as e:
        print(str(e))
        sys.exit(1)
Esempio n. 48
0
 def __init__(self, connection_file):
     settings = read_settings(connection_file)
     self.connection = pg8000.connect(
                             user=settings['db-user'],
                             password=settings['db-password'],
                             database=settings['db-database'])
Esempio n. 49
0
import pg8000

db = {
    "DB_HOST": "localhost",
    "DB": "PostgreSQL 9.6",
    "DB_USER": "******",
    "DB_PORT": 5432
}

conn = pg8000.connect(host=db["DB_HOST"],
                      port=db["DB_PORT"],
                      user=db["DB_USER"],
                      password='******')

cursor = conn.cursor()
cursor.execute("CREATE TEMPORARY TABLE blacklist (id SERIAL, title TEXT)")
cursor.execute(
    "INSERT INTO blacklist (title) VALUES (%s), (%s), (%s) RETURNING id, title",
    ("*****@*****.**", "*****@*****.**",
     "*****@*****.**"))
results = cursor.fetchall()
for row in results:
    id, title = row
    print("id = %s, title = %s" % (id, title))
s = "SELECT title from blacklist where title like 'n%'"
cursor.execute(s)
print(cursor.fetchall())


def deleter(term):
    s = "DELETE FROM blacklist where title = '" + term + "'"
Esempio n. 50
0
                              text="Stücklisten",
                              values=("", "", ""))
    for part in getPartLists(cur):
        tree.insert(partlistTab,
                    "end",
                    f"d{part[0]}",
                    text='',
                    values=(part[1], part[2], part[3]))

    tree.pack(side=tk.LEFT, fill=tk.BOTH)

    master.mainloop()


if __name__ == "__main__":
    con = pg8000.connect(user="******",
                         host='192.168.2.68',
                         password="",
                         database="sven")
    cur = con.cursor()

    #createCustomer(cur)
    #createParts(cur)
    #createProjects(cur)
    #createPartLists(cur)

    showDatabase(cur)

    con.commit()
    con.close()
Esempio n. 51
0
def main():
    import socket
    import pg8000
    import dpkt
    from sys import exit
    import python_TZSP
    # import python_sys_log_sql
    # 配置连接IP和端口号
    UDP_IP = '0.0.0.0'
    UDP_PORT = 9999

    # 创建socket,IPv4,UDP协议的实例
    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)

    # 对实例端口进行绑定
    sock.bind((UDP_IP, UDP_PORT))
    # encoding = "utf-8"
    conn = pg8000.connect(host='127.0.0.1',
                          user='******',
                          password='******',
                          database='mklogdb')
    cursor = conn.cursor()
    while True:
        try:
            # 通过socket接收数据
            data, addr = sock.recvfrom(1024)
            # Test
            # print('RAW data is :', str(data))
            # print('RAW addr is :', addr)
            #
            # print('TZSP type is:', getType(data))
            # print('TZSP protocol is:', getProtocol(data))
            # print('TZSP TagType is:', getTagType(data))

            # 截取封装数据包
            encapsulated_packet_data = python_TZSP.get_encapsulated_packet(
                data)
            # 获取源mac地址
            src_mac = python_TZSP.eth_addr(encapsulated_packet_data[6:12])
            eth = dpkt.ethernet.Ethernet(encapsulated_packet_data)
            ip = eth.data
            src = socket.inet_ntoa(ip.src)
            dst = socket.inet_ntoa(ip.dst)
            # print('dpkt src: ', src, ' dpkt dst: ', dst)

            tcp = ip.data

            # print(src_mac)
            print(src)
            # print(dst)
            # http = dpkt.http.Response(encapsulated_packet_data)
            # print(http)

            if tcp.dport == 80 and len(tcp.data) > 0:
                http = dpkt.http.Request(tcp.data)
                if http.uri is not None:
                    if http.uri[-4:] not in (
                            '.jpg', '.png', '.gif', '.css',
                            '.ico') and http.uri[-3:] not in '.js':
                        # print('uri is :::', http.uri)
                        # print('http header is :::', http.headers)
                        URL = http.headers['host'] + http.uri
                        # if 'user-agent' in http.headers.keys():
                        #     print('user-agent is :', http.headers['user-agent'])
                        #     print('http header is :::', http.headers)
                        # print('i_src_ip is :', src)
                        # print('i_dst_ip is :', dst)
                        # print('i_src_mac is :', src_mac)
                        print('URL is :', URL)
                        # print('i_v_system is :', http.headers['user-agent'])
                        src_str = "\'" + str(src) + "\'"
                        dst_str = "\'" + str(dst) + "\'"
                        src_mac_str = "\'" + str(src_mac) + "\'"
                        URL_str = "\'" + str(URL) + "\'"
                        http_headers_str = "\'" + str(
                            http.headers['user-agent']) + "\'"
                        str_execute = "insert into urllist(src_ip, dst_ip, src_mac, url, v_system, v_time) values (" + src_str + "," + dst_str + "," + src_mac_str + "," + URL_str + "::varchar(500), " + http_headers_str + "::varchar(150), current_timestamp)"
                        # print(str_execute)
                        cursor.execute(str_execute)
                        conn.commit()
            elif tcp.dport == 443 and len(tcp.data) > 0:
                pass
        except Exception as e:
            print(str(e))
            continue
        except KeyboardInterrupt:
            print('程序终止。')
            sock.close()
            exit()
Esempio n. 52
0
def get_pg_conn():
    global db_connections
    pid = str(os.getpid())

    conn = None

    # get the database connection for this PID
    try:
        conn = db_connections[pid]
    except KeyError:
        pass

    if conn is None:
        # connect to the database
        if debug:
            comment('Connect [%s] %s:%s:%s:%s' % (pid, db_host, db_port, db, db_user))

        try:
            conn = pg8000.connect(user=db_user, host=db_host, port=db_port, database=db, password=db_pwd,
                                  ssl=ssl, timeout=None)
            # Enable keepalives manually untill pg8000 supports it
            # For future reference: https://github.com/mfenniak/pg8000/issues/149
            # TCP keepalives still need to be configured appropriately on OS level as well
            conn._usock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
            conn.autocommit = True
        except Exception as e:
            print(e)
            print('Unable to connect to Cluster Endpoint')
            cleanup(conn)
            return ERROR

        aws_utils.set_search_paths(conn, schema_name, target_schema)

        if query_group is not None:
            set_query_group = 'set query_group to %s' % query_group

            if debug:
                comment(set_query_group)

            run_commands(conn, [set_query_group])

        if query_slot_count is not None and query_slot_count != 1:
            set_slot_count = 'set wlm_query_slot_count = %s' % query_slot_count

            if debug:
                comment(set_slot_count)

            run_commands(conn, [set_slot_count])

        # set a long statement timeout
        set_timeout = "set statement_timeout = '%s'" % statement_timeout
        if debug:
            comment(set_timeout)

        run_commands(conn, [set_timeout])

        # set application name
        set_name = "set application_name to 'ColumnEncodingUtility-v%s'" % __version__

        if debug:
            comment(set_name)

        run_commands(conn, [set_name])

        # Set search_path
        set_searchpath = "set search_path to '$user', public, %s;" % schema_name
        if debug:
            comment(set_searchpath)

        run_commands(conn, [set_searchpath])

        # turn off autocommit for the rest of the executions
        conn.autocommit = False

        # cache the connection
        db_connections[pid] = conn

    return conn
Esempio n. 53
0
def connect(
    connection: Optional[str] = None,
    secret_id: Optional[str] = None,
    catalog_id: Optional[str] = None,
    dbname: Optional[str] = None,
    boto3_session: Optional[boto3.Session] = None,
    ssl_context: Optional[Dict[Any, Any]] = None,
    timeout: Optional[int] = None,
    tcp_keepalive: bool = True,
) -> pg8000.Connection:
    """Return a pg8000 connection from a Glue Catalog Connection.

    https://github.com/tlocke/pg8000

    Parameters
    ----------
    connection : Optional[str]
        Glue Catalog Connection name.
    secret_id: Optional[str]:
        Specifies the secret containing the version that you want to retrieve.
        You can specify either the Amazon Resource Name (ARN) or the friendly name of the secret.
    catalog_id : str, optional
        The ID of the Data Catalog.
        If none is provided, the AWS account ID is used by default.
    dbname: Optional[str]
        Optional database name to overwrite the stored one.
    boto3_session : boto3.Session(), optional
        Boto3 Session. The default boto3 session will be used if boto3_session receive None.
    ssl_context: Optional[Dict]
        This governs SSL encryption for TCP/IP sockets.
        This parameter is forward to pg8000.
        https://github.com/tlocke/pg8000#functions
    timeout: Optional[int]
        This is the time in seconds before the connection to the server will time out.
        The default is None which means no timeout.
        This parameter is forward to pg8000.
        https://github.com/tlocke/pg8000#functions
    tcp_keepalive: bool
        If True then use TCP keepalive. The default is True.
        This parameter is forward to pg8000.
        https://github.com/tlocke/pg8000#functions

    Returns
    -------
    pg8000.Connection
        pg8000 connection.

    Examples
    --------
    >>> import awswrangler as wr
    >>> con = wr.postgresql.connect("MY_GLUE_CONNECTION")
    >>> with con.cursor() as cursor:
    >>>     cursor.execute("SELECT 1")
    >>>     print(cursor.fetchall())
    >>> con.close()

    """
    attrs: _db_utils.ConnectionAttributes = _db_utils.get_connection_attributes(
        connection=connection,
        secret_id=secret_id,
        catalog_id=catalog_id,
        dbname=dbname,
        boto3_session=boto3_session)
    if attrs.kind != "postgresql":
        raise exceptions.InvalidDatabaseType(
            f"Invalid connection type ({attrs.kind}. It must be a postgresql connection.)"
        )
    return pg8000.connect(
        user=attrs.user,
        database=attrs.database,
        password=attrs.password,
        port=attrs.port,
        host=attrs.host,
        ssl_context=ssl_context,
        timeout=timeout,
        tcp_keepalive=tcp_keepalive,
    )
Esempio n. 54
0
        :param validation_timeout: Timeout to try to validate the connection
        :return: pg8000 connection
        """
        Redshift._validate_connection(database=database,
                                      host=host,
                                      port=port,
                                      user=user,
                                      password=password,
                                      tcp_keepalive=tcp_keepalive,
                                      application_name=application_name,
                                      validation_timeout=validation_timeout)
        conn = pg8000.connect(database=database,
                              host=host,
                              port=int(port),
                              user=user,
                              password=password,
                              ssl=True,
                              application_name=application_name,
                              tcp_keepalive=tcp_keepalive,
                              timeout=connection_timeout)
        cursor = conn.cursor()
        cursor.execute(f"set statement_timeout = {statement_timeout}")
        conn.commit()
        cursor.close()
        return conn

    def get_connection(self, glue_connection):
        conn_details = self._session.glue.get_connection_details(name=glue_connection)
        props = conn_details["ConnectionProperties"]
        host = props["JDBC_CONNECTION_URL"].split(":")[2].replace("/", "")
        port, database = props["JDBC_CONNECTION_URL"].split(":")[3].split("/")
Esempio n. 55
0
        self._id = id
        self._city = city
        self._state = state
        self._country = country
        self._latt = latt
        self._long = long

    def __str__(self):
        return f"{self.__dict__}"


st = time.time()
conn = None
try:
    conn = pg8000.connect(user='******',
                          password='******',
                          database='testdb')
    with conn.cursor() as cursor:
        cursor.execute('select * from spectrum_markets_geocode')
        results = cursor.fetchall()

        codes = []
        for row in results:
            c = Geocode(id=row[0],
                        city=row[1],
                        state=row[2],
                        country=row[3],
                        latt=row[4],
                        long=row[5])
            codes.append(row)
def get_md5_config():
    # 连接数据库
    conn = pg8000.connect(host=psql_ip,
                          user=psql_username,
                          password=psql_password,
                          database=psql_db_name)
    cursor = conn.cursor()
    # 查询数据库qytdb_devicedb,获取ip, type, name, snmp_ro_community, ssh_username, ssh_password, enable_password等信息
    cursor.execute(
        "select ip, type, name, snmp_ro_community, ssh_username, ssh_password, enable_password from qytdb_devicedb"
    )
    result = cursor.fetchall()
    for device in result:
        if device[1] == 'IOS Router':  # 如果设备是路由器
            # 获取设备show run
            run_config_raw = ssh_singlecmd(str(device[0]), device[4],
                                           device[5], 'show run')
            list_run_config = run_config_raw.split('\n')
            location = 0
            host_location = 0  # 用来找到hostname出现的位置
            for i in list_run_config:
                if re.match('.*hostname .*', i):
                    host_location = location  # 定位hostname所在位置
                else:
                    location += 1
            list_run_config = list_run_config[
                host_location:]  # 截取hostname开始往后的部分
            run_config = '\n'.join(list_run_config)  # 再次还原为字串形式的配置

            # 计算获取配置的MD5值
            m = hashlib.md5()
            m.update(run_config.encode())
            md5_value = m.hexdigest()
            # 获取最近一次保存配置的MD5值,注意添加设备的时候就会做一次备份!
            # order by date desc limit 1 查询最近一次记录
            cursor.execute(
                "select hash from qytdb_deviceconfig where name = '" +
                device[2] + "' order by date desc limit 1")
            result = cursor.fetchall()
            if result[0][0] == md5_value:  # 如果本次配置的MD5值,与上一次备份配置的MD5值相同!略过此次操作
                continue
            else:
                # 如果本次配置的MD5值,与上一次备份配置的MD5值不相同,备份配置与MD5值到数据库
                sqlcmd = "INSERT INTO qytdb_deviceconfig (name, hash, config, date) VALUES ('" + device[
                    2] + "', '" + md5_value + "', '" + run_config + "', '" + str(
                        datetime.now()) + "')"
                cursor.execute(sqlcmd)
                conn.commit()

        elif device[1] == 'Nexus Switch':  # 如果设备是交换机
            # 获取设备show run
            run_config_raw = ssh_singlecmd(str(device[0]), device[4],
                                           device[5], 'show run')
            list_run_config = run_config_raw.split('\n')
            location = 0
            host_location = 0  # 用来找到hostname出现的位置
            for i in list_run_config:
                if re.match('.*hostname .*', i):
                    host_location = location  # 定位hostname所在位置
                else:
                    location += 1
            list_run_config = list_run_config[
                host_location:]  # 截取hostname开始往后的部分
            run_config = '\n'.join(list_run_config)  # 再次还原为字串形式的配置

            # 计算获取配置的MD5值
            m = hashlib.md5()
            m.update(run_config.encode())
            md5_value = m.hexdigest()

            # 获取最近一次保存配置的MD5值,注意添加设备的时候就会做一次备份!
            # order by date desc limit 1 查询最近一次记录
            cursor.execute(
                "select hash from qytdb_deviceconfig where name = '" +
                device[2] + "' order by date desc limit 1")
            result = cursor.fetchall()
            if result[0][0] == md5_value:  # 如果本次配置的MD5值,与上一次备份配置的MD5值相同!略过此次操作
                continue
            else:
                # 如果本次配置的MD5值,与上一次备份配置的MD5值不相同,备份配置与MD5值到数据库
                sqlcmd = "INSERT INTO qytdb_deviceconfig (name, hash, config, date) VALUES ('" + device[
                    2] + "', '" + md5_value + "', '" + run_config + "', '" + str(
                        datetime.now()) + "')"
                cursor.execute(sqlcmd)
                conn.commit()

        elif device[1] == 'ASA Firewall':  # 如果设备是ASA
            # 获取设备show run, 注意获取ASA配置的方法不一样
            run_config_raw = ssh_multicmd_asa(str(
                device[0]), device[4], device[5], [
                    'enable', device[6], 'terminal pager 0',
                    'more system:running-config'
                ])
            list_run_config = run_config_raw.split('\n')
            location = 0
            host_location = 0  # 用来找到hostname出现的位置
            for i in list_run_config:
                if re.match('^hostname .*',
                            i):  # 注意匹配hostname的方法不一样,因为配置中会多次出现hostname
                    host_location = location  # 定位hostname所在位置
                else:
                    location += 1
            list_run_config = list_run_config[
                host_location:-4]  # 截取hostname开始往后的部分, 去除最后一些无用部分
            run_config = '\n'.join(list_run_config)  # 再次还原为字串形式的配置

            # 计算获取配置的MD5值
            m = hashlib.md5()
            m.update(run_config.encode())
            md5_value = m.hexdigest()

            # 获取最近一次保存配置的MD5值,注意添加设备的时候就会做一次备份!
            # order by date desc limit 1 查询最近一次记录
            cursor.execute(
                "select hash from qytdb_deviceconfig where name = '" +
                device[2] + "' order by date desc limit 1")
            result = cursor.fetchall()
            if result[0][0] == md5_value:  # 如果本次配置的MD5值,与上一次备份配置的MD5值相同!略过此次操作
                continue
            else:
                # 如果本次配置的MD5值,与上一次备份配置的MD5值不相同,备份配置与MD5值到数据库
                sqlcmd = "INSERT INTO qytdb_deviceconfig (name, hash, config, date) VALUES ('" + device[
                    2] + "', '" + md5_value + "', '" + run_config + "', '" + str(
                        datetime.now()) + "')"
                cursor.execute(sqlcmd)
                conn.commit()
def get_pg_conn():
    global db_connections
    pid = str(os.getpid())

    conn = None

    # get the database connection for this PID
    try:
        conn = db_connections[pid]
    except KeyError:
        pass

    if conn is None:
        # connect to the database
        if debug:
            comment('Connect [%s] %s:%s:%s:%s' %
                    (pid, db_host, db_port, db, db_user))

        try:
            conn = pg8000.connect(user=db_user,
                                  host=db_host,
                                  port=db_port,
                                  database=db,
                                  password=db_pwd,
                                  ssl=ssl,
                                  timeout=None)
        except Exception as e:
            print(e)
            print('Unable to connect to Cluster Endpoint')
            cleanup(conn)
            return ERROR

            # set default search path
        search_path = 'set search_path = \'$user\',public,%s' % schema_name
        if target_schema is not None and target_schema != schema_name:
            search_path = search_path + ', %s' % target_schema

        if debug:
            comment(search_path)

        cursor = None
        try:
            cursor = conn.cursor()
            cursor.execute(search_path)
        except pg8000.Error as e:
            if re.match('schema "%s" does not exist' % schema_name,
                        e.message) is not None:
                print('Schema %s does not exist' % schema_name)
            else:
                print(e.message)
            return None

        if query_group is not None:
            set_query_group = 'set query_group to %s' % query_group

            if debug:
                comment(set_query_group)

            cursor.execute(set_query_group)

        if query_slot_count is not None and query_slot_count != 1:
            set_slot_count = 'set wlm_query_slot_count = %s' % query_slot_count

            if debug:
                comment(set_slot_count)

            cursor.execute(set_slot_count)

        # set a long statement timeout
        set_timeout = "set statement_timeout = '1200000'"
        if debug:
            comment(set_timeout)

        cursor.execute(set_timeout)

        # set application name
        set_name = "set application_name to 'ColumnEncodingUtility-v%s'" % __version__

        if debug:
            comment(set_name)

        cursor.execute(set_name)

        # cache the connection
        db_connections[pid] = conn

    return conn
Esempio n. 58
0
#亁颐堂官网www.qytang.com
#乾颐盾是由亁颐堂现任明教教主开发的综合性安全课程
#包括传统网络安全(防火墙,IPS...)与Python语言和黑客渗透课程!

import hashlib as hasher
import datetime as date
from random import randint
import json
import rsa_sign
from QYTCoin_Class import *

#初始化数据库,创建表qytcoin_chain
import pg8000

conn = pg8000.connect(host='10.1.1.1',
                      user='******',
                      password='******',
                      database='qytangdb')
cursor = conn.cursor()
cursor.execute("drop table qytcoin_chain")
cursor.execute(
    "CREATE TABLE qytcoin_chain (index int,chain_datetime timestamp,previous_hash varchar(1000),transaction_data varchar(9999),transaction_data_id int,secure_code varchar(60),hash varchar(1000))"
)
conn.commit()


#创世区块
def create_genesis_block():
    #创世区块创建的时间
    genesis_block_datetime = date.datetime.now()
    #把创世区块插入数据库,除了时间都为空
    conn = pg8000.connect(host='10.1.1.1',
Esempio n. 59
0
def handle(event, context):
    try:
        connection = pg8000.connect(host=rds_host,
                                    port=5432,
                                    user=rds_username,
                                    password=rds_password,
                                    database=rds_db_name,
                                    connect_timeout=5)
    except:
        logger.error(
            "ERROR: Unexpected error: Could not connect to Postgres RDS instance."
        )
        sys.exit()

    source = event['data']['object']['source']
    cur = connection.cursor()
    # ---------------- DELETE EVENT AND INSERT ------------------------
    created = datetime.datetime.fromtimestamp(int(
        event['created'])).strftime('%Y-%m-%d %H:%M:%S')

    data = (event['id'], event['livemode'], event['type'], created)
    try:
        cur.execute("DELETE FROM stripe.event WHERE id = '" + event['id'] +
                    "'")
        cur.execute(
            "INSERT INTO stripe.event (id, livemode, type, created) VALUES (%s, %s, %s, %s)",
            data)
    except pg8000.Error as e:
        response = {
            "status": "Fail",
            "message": "Database error while handling events",
            "error": e
        }
        return response

    #this is a charge event, so we insert charges and respective billing data
    # ---------------- DELETE EVENT AND INSERT ------------------------
    charge_event = event['data']['object']
    created = datetime.datetime.fromtimestamp(int(
        charge_event['created'])).strftime('%Y-%m-%d %H:%M:%S')
    data = (charge_event['id'], charge_event["customer"], event['id'],
            charge_event["invoice"], charge_event["livemode"],
            charge_event["paid"], charge_event["amount"],
            charge_event["currency"], charge_event["refunded"],
            charge_event["captured"], charge_event["balance_transaction"],
            charge_event["failure_message"], charge_event["failure_code"],
            charge_event["amount_refunded"], charge_event["description"],
            charge_event["dispute"], created)
    try:
        cur.execute("DELETE FROM stripe.charge WHERE id = '" +
                    event['data']['object']['id'] + "'")
        cur.execute(
            """INSERT INTO stripe.charge (id, event_id, customer_id, invoice_id, livemode,
                                             paid, amount, currency, refunded, captured,
                                             balance_transaction, failure_message, failure_code,
                                            amount_refunded, description, dispute, created)                                       
                   VALUES (%s, %s, %s, %s, %s,
                            %s, %s, %s, %s, %s, 
                            %s, %s, %s,
                            %s, %s, %s, %s
                            )""", data)
    except pg8000.Error as e:
        response = {
            "status": "Fail",
            "message": "Database error while handling charges"
        }
        return response

    #connection.commit()
    # ---------------- HANDLE BILLING DETAILS ------------------------

    billing_details = event['data']['object']['source']
    data = (
        billing_details['id'],
        billing_details["name"],
        billing_details["customer"],
        billing_details["object"],
        billing_details['last4'],
        billing_details["type"],
        billing_details["brand"],
        billing_details["exp_month"],
        billing_details["exp_year"],
        billing_details["fingerprint"],
        billing_details["country"],
        billing_details["address_line1"],
        billing_details["address_line2"],
        billing_details["address_state"],
        billing_details["address_zip"],
        billing_details["address_country"],
        billing_details["cvc_check"],
        billing_details["address_line1_check"],
        billing_details["address_zip_check"],
    )
    try:
        cur.execute("DELETE FROM stripe.billing WHERE id = '" +
                    billing_details['id'] + "'")
        cur.execute(
            """INSERT INTO stripe.billing ("id", "name", "customer_id", "object",
                                                "last4","type","brand",
                                                "exp_month","exp_year",
                                                "fingerprint",
                                                "country", 
                                                "address_line1", "address_line2", 
                                                "address_state", "address_zip", "address_country",
                                                "cvc_check", "address_line1_check", "address_zip_check") 
                     VALUES (%s, %s, %s, %s,
                              %s, %s, %s,
                              %s, %s,
                              %s,
                              %s,
                              %s, %s,
                              %s, %s, %s,
                              %s, %s, %s)""", data)
    except pg8000.Error as e:
        response = {
            "status": "Fail",
            "message": "Database error while handling billing"
        }
        return response
    # ---------------- HANDLE BILLING DETAILS JOIN TABLE ------------------------
    data = (
        charge_event['id'],
        billing_details["id"],
    )

    try:
        cur.execute("DELETE FROM stripe.charge_billing WHERE billing_id = '" +
                    billing_details['id'] + "' AND charge_id = '" +
                    charge_event['id'] + "'")
        cur.execute(
            """INSERT INTO stripe.charge_billing("charge_id", "billing_id") 
                   VALUES (%s, %s)""", data)
    except pg8000.Error as e:
        response = {
            "status": "Fail",
            "message": "Database error while handling charges and billing"
        }

        return response

    connection.commit()
    connection.close()
    logger.error("SUCCESS: copied to Postgres RDS instance.")
    return source
Esempio n. 60
0
 def connect_to_postgres():
     try:
         return connect(host=host, user=user, port=port,
                        database=database)
     except (InterfaceError, ProgrammingError):
         return False