def get_hit_count(): retries = 5 while True: try: phoenixdb.connect("http://pqs:8765", autocommit=True) except phoenixdb.errors.InterfaceError as exc: raise exc html = "<h3>Hello from {name}!</h3>" \ "Hostname: <b>{hostname}</b><br/>" return html.format(name=os.getenv("NAME", "Standalone Flask App"), hostname=socket.gethostname())
def create_jobtable(): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute(''' CREATE TABLE analysis_job ( jobId Integer PRIMARY KEY, jobName VARCHAR(200) , jobPlace VARCHAR(200) , jobSalary VARCHAR(100) , jobAdvantage VARCHAR(300) , releaseTime VARCHAR(300) , jobNeed VARCHAR(300) , educationRequire VARCHAR(300) , experienceRequire VARCHAR(300) , skillRequire VARCHAR(300) , jobLink VARCHAR(500) , jobInfo VARCHAR , jobNature VARCHAR(200) , jobLabels VARCHAR(300) , company_id Integer NULL, clicktimes Integer DEFAULT 0 , keyword VARCHAR(100) NULL ) ''')
def connect_db(): print("Making new database connection") REQUIRED_OPTS = ['Username', 'Password', 'Url'] config = configparser.ConfigParser() config.read('config.ini') if not 'COD' in config: raise Exception("Could not find section for COD in config.ini") cod_config = config['COD'] opts = {} # Validate the configuration for required_opt in REQUIRED_OPTS: if not required_opt in cod_config: raise Exception("Did not find %s in configuration" % (required_opt)) # Provide non-required options if 'Truststore' in cod_config: opts['verify'] = cod_config['Truststore'] if 'Authentication' in cod_config: opts['authentication'] = cod_config['Authentication'] else: opts['authentication'] = 'BASIC' # Read required options opts['avatica_user'] = cod_config['Username'] opts['avatica_password'] = cod_config['Password'] db = phoenixdb.connect(cod_config['Url'], autocommit=True, **opts) return db
def run_query(self, query, user): logger.error("Lindorm running query: %s", query) connection = phoenixdb.connect(url=self.configuration.get("url", "") + ":" + str(self.configuration.get("port", "")), autocommit=True) cursor = connection.cursor() try: cursor.execute(self.del_comments(query)) column_tuples = [(i[0], TYPES_MAPPING.get(i[1], None)) for i in cursor.description] columns = self.fetch_columns(column_tuples) rows = [ dict(zip(([column["name"] for column in columns]), r)) for i, r in enumerate(cursor.fetchall()) ] data = {"columns": columns, "rows": rows} json_data = json_dumps(data) error = None cursor.close() except Error as e: json_data = None error = "code: {}, sql state:{}, message: {}, Query:{}".format( e.code, e.sqlstate, str(e), query) finally: if connection: connection.close() return json_data, error
def count_djob(): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("select count(*) from analysis_digitizedjob") return cursor.fetchone()[0]
def getjob_byjobid(jobid): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("select * from analysis_job where jobid = " + str(jobid)) return cursor.fetchone()
def connect_to_database(): log.info("Database Connection") REQUIRED_OPTS = [ 'Username', 'Password', 'Authentication', 'Url', 'Serialization' ] config = configparser.ConfigParser() config.read('config.ini') if not 'COD' in config: raise Exception("Could not find section for COD in config.ini") cod_config = config['COD'] opts = {} # Validate the configuration for required_opt in REQUIRED_OPTS: if not required_opt in cod_config: raise Exception("Did not find %s in configuration" % (required_opt)) # Read required options opts['avatica_user'] = cod_config['Username'] opts['avatica_password'] = cod_config['Password'] opts['authentication'] = cod_config['Authentication'] opts['serialization'] = cod_config['serialization'] try: db = phoenixdb.connect(cod_config['Url'], autocommit=True, **opts) except Exception as e: print(f"\tDATABASE CONNECTION ERROR: {str(e)}") log.error(e) raise return db
def get_connection(self, db_name=None): if self.conn: return self.conn database_url = f'http://{self.host}:{self.port}/' self.conn = phoenixdb.connect(database_url, autocommit=True) return self.conn
def insert_company(com=None): """ :param com: 一个包含所有company字段的元组 :return: """ if com is None: com = [ 32836, '达疆网络科技(上海)有限公司', '500-2000人', 'O2O', '[' '年底双薪' ', ' '绩效奖金' ', ' '岗位晋升' ', ' '定期体检' ']', 'https://www.lagou.com/gongsi/32836.html', '[' '达达-京东到家,是中国优质物流信息服务平台和生鲜商超O2O平台,同城速递信息服务平台“达达”目前已经覆盖全国360多个重要城市,拥有300多万众包配送员用户,80多万商家用户和3000万个人用户并为其提供平台信息服务,日单量峰值超过400万单。其生鲜商超O2O平台“京东到家”,包含超市便利、新鲜果蔬、零食小吃、鲜花烘焙、医药健康等业务,覆盖北京、上海、广州等22个城市,注册用户超过3000万。公司成立于2014年初,先后获得了红杉、DST、京东、沃尔玛等顶级基金和战略合作伙伴的投资,累计融资金额近7亿美金。' ']', 'nan', 0, 0, '[' '东外滩' ', ' '八佰伴' ', ' '梅园' ']', 'http://www.imdada.cn', 'i/image/M00/40/E3/CgpFT1lbMmCAJyQhAAA1-lNtzeQ674.png', 'D轮及以上' ] conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute( ''' upsert INTO analysis_company (companyId, compName, compSize, compIndustry, companyLabels, compLink, compIntroduce, contactInfo, longitude, latitude, businessZones, compHome, companyLogo, financeStage) VALUES(?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?) ''', com)
def run_query(self, query, user): connection = phoenixdb.connect( url=self.configuration.get('url', ''), autocommit=True) cursor = connection.cursor() try: cursor.execute(query) column_tuples = [(i[0], TYPES_MAPPING.get(i[1], None)) for i in cursor.description] columns = self.fetch_columns(column_tuples) rows = [dict(zip(([c['name'] for c in columns]), r)) for i, r in enumerate(cursor.fetchall())] data = {'columns': columns, 'rows': rows} json_data = json_dumps(data) error = None cursor.close() except Error as e: json_data = None error = 'code: {}, sql state:{}, message: {}'.format(e.code, e.sqlstate, e.message) except (KeyboardInterrupt, InterruptException) as e: error = "Query cancelled by user." json_data = None except Exception as ex: json_data = None error = unicode(ex) finally: if connection: connection.close() return json_data, error
def test_dict_cursor_default_attribute(self): db = phoenixdb.connect(TEST_DB_URL, autocommit=True) db.cursor_factory = phoenixdb.cursor.DictCursor self.addCleanup(db.close) with db.cursor() as cursor: self._check_dict_cursor(cursor)
def select_all_hbase(): database_url = 'http://localhost:8765/' conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() a = cursor.execute("SELECT * FROM ROUTES2") routes_list = [] data = cursor.fetchall() data_numrows = len(data) print "[log] jumlah baris: ", len(data) for row in data : route_temp = { 'id_route': row[0], 'airline': row[1], 'id_airline': row[2], 'src_airport': row[3], 'id_src_airport': row[4], 'dst_airport': row[5], 'id_dst_airport': row[6], 'codeshare': row[7], 'stop_val': row[8], 'equipment': row[9], 'log_date': row[10] } routes_list.append(route_temp) result = { "Flight_Rows" : data_numrows, "Flight_Routes" : routes_list } return jsonify(result)
def run_query(self, query, user): connection = phoenixdb.connect(url=self.configuration.get('url', ''), autocommit=True) cursor = connection.cursor() try: cursor.execute(query) column_tuples = [(i[0], TYPES_MAPPING.get(i[1], None)) for i in cursor.description] columns = self.fetch_columns(column_tuples) rows = [ dict(zip(([c['name'] for c in columns]), r)) for i, r in enumerate(cursor.fetchall()) ] data = {'columns': columns, 'rows': rows} json_data = json_dumps(data) error = None cursor.close() except Error as e: json_data = None error = 'code: {}, sql state:{}, message: {}'.format( e.code, e.sqlstate, e.message) except (KeyboardInterrupt, InterruptException) as e: error = "Query cancelled by user." json_data = None except Exception as ex: json_data = None error = unicode(ex) finally: if connection: connection.close() return json_data, error
def __init__(self, address): url = 'http://{}/'.format(address) logging.debug('Connect HBase: {}'.format(url)) try: self.conn = phoenixdb.connect(url, autocommit=True) except Exception as e: raise Exception('连接异常>>> {}'.format(e))
def setUp(self): self.conn = phoenixdb.connect(TEST_DB_URL, autocommit=True, **httpArgs) def closeDb(): self.conn.close() self.addCleanup(closeDb)
def get_cityjobsalary(city, **kwargs): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() start = kwargs.get('start') end = kwargs.get('end') if (end is None): start = str(start) cursor.execute(""" SELECT COUNT(JOBID) AS s FROM ANALYSIS_JOB AS j JOIN ANALYSIS_DIGITIZEDJOB AS d ON j.JOBID=d.job_id WHERE d.salary > """ + start + """ AND j.JOBPLACE LIKE '""" + city + """%' """) result = cursor.fetchone()[0] else: start = str(start) end = str(end) cursor.execute(""" SELECT COUNT(JOBID) AS s FROM ANALYSIS_JOB AS j JOIN ANALYSIS_DIGITIZEDJOB AS d ON j.JOBID=d.job_id WHERE d.salary <= """ + start + """ AND d.SALARY>""" + end + """ AND j.JOBPLACE LIKE '""" + city + """%' """) result = cursor.fetchone()[0] print(city, start, end, result) return result
def getConn(self, DATABASE_URL): """ :param DATABASE_URL:phoenix 服务的地址 ,记得启动phoenix的querysever.py 服务 :return: """ conn = phoenixdb.connect(DATABASE_URL, autocommit=True) cursor = conn.cursor() return conn, cursor
def Init(self): try: database_url = 'http://{}:{}/'.format(self.host, self.port) conn = phoenixdb.connect(database_url, autocommit=True) return conn except: Logging(msg=traceback.format_exc(), level='error') return None
def ntest_phoenix(): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("SELECT * FROM analysis_company ") all = cursor.fetchall() for item in all: print(item)
def getcompsize_bycompid(compid): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("select compsize from analysis_company where COMPANYID = " + str(compid)) return cursor.fetchone()[0]
def getdjob_byjobid(jobid): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("select * from ANALYSIS_DIGITIZEDJOB where job_id = " + str(jobid)) return cursor.fetchone()
def mysql_to_phoenixdb(user_name, repo_name): connection = phoenixdb.connect('10.28.32.114:8765', autocommit=True) print(connection) cursor = connection.cursor() Record.prepare(user_name, repo_name) query = session.query(Record).filter(Record.record_date == datetime.date(2017, 8, 6)) \ .order_by(Record.record_date, Record.record_time, Record.version_id, Record.line_no)
def __init__(self): try: self.client = docker.from_env() self.dbconn = phoenixdb.connect(self.dburl, autocommit=True) except Exception as inst: print(type(inst)) print(inst.args) print(inst) sys.exit(1)
def __init__(self, **kwargs): # http://localhost:8765/ self.dburl = kwargs['dburl'] # autocommit = True self.autocommit = kwargs['autocommit'] self.conn = phoenixdb.connect(self.dburl, autocommit=self.autocommit) pass
def __init__(self): opts = {} opts['authentication'] = 'BASIC' opts['avatica_user'] = config.WORKLOAD_USER opts['avatica_password'] = config.WORKLOAD_PASSWORD database_url = config.OPDB_ENDPOINT self.TABLENAME = "users" self.conn = phoenixdb.connect(database_url, autocommit=True, **opts) self.curs = self.conn.cursor()
def __init__(self, url=default_phoenix_url): self.phoenix_url = url try: self.connection = phoenixdb.connect(self.phoenix_url, autocommit=True) except Exception as e: self.connection = None logInfo('phoenix connection') logInfo(e)
def __init__(self): opts = {} opts['authentication'] = 'BASIC' opts['avatica_user'] = os.environ["WORKLOAD_USER"] opts['avatica_password'] = os.environ["WORKLOAD_PASSWORD"] database_url = os.environ["OPDB_ENDPOINT"] self.TABLENAME = "stocks_feed" self.conn = phoenixdb.connect(database_url, autocommit=True,**opts) self.curs = self.conn.cursor()
def getjob_bykeyword(keyword): conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("select * from analysis_job where keyword = '" + keyword + "'") jobs = cursor.fetchall() for job in jobs: print(job)
def __init__(self): opts = {} opts['authentication'] = 'BASIC' opts['avatica_user'] = '******' opts['avatica_password'] = '******' database_url = "<cod thin jdbc url>" self.TABLENAME = "users" self.conn = phoenixdb.connect(database_url, autocommit=True,**opts) self.curs = self.conn.cursor()
def _connect(self, conf): conf = copy.deepcopy(conf) self.max_op_fail_retry = conf.pop('max_op_fail_retry', 0) self.default_timeout = conf.pop('timeout', DEFAULT_TIMEOUT) conn = phoenixdb.connect(random.choice(conf['urls']), autocommit=True) cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) return conn, cursor
def connect_to_phoenix(): try: db = phoenixdb.connect(url=config.PHOENIX_QUERY_SERVER_URI, max_retries=2, autocommit=True) except Exception as e: print("PhoenixDB Failure Exception:\n %s" % str(e)) else: return db
def test_select_parameter(self): db = phoenixdb.connect(TEST_DB_URL, autocommit=True) self.addCleanup(db.close) with db.cursor() as cursor: cursor.execute("DROP TABLE IF EXISTS test") cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.executemany("UPSERT INTO test VALUES (?, ?)", [[i, 'text {}'.format(i)] for i in range(10)]) with db.cursor() as cursor: cursor.itersize = 4 cursor.execute("SELECT * FROM test WHERE id>? ORDER BY id", [1]) self.assertEqual(cursor.fetchall(), [[i, 'text {}'.format(i)] for i in range(2, 10)])
def test_schema(self): db = phoenixdb.connect(TEST_DB_URL, autocommit=True) self.addCleanup(db.close) with db.cursor() as cursor: try: cursor.execute("CREATE SCHEMA IF NOT EXISTS test_schema") except InternalError as e: if "phoenix.schema.isNamespaceMappingEnabled" in e.message: self.skipTest(e.message) raise cursor.execute("DROP TABLE IF EXISTS test_schema.test") cursor.execute("CREATE TABLE test_schema.test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test_schema.test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test_schema.test ORDER BY id") self.assertEqual(cursor.fetchall(), [[1, 'text 1']])
def setUp(self): self.conn = phoenixdb.connect(TEST_DB_URL, autocommit=True) self.cleanup_tables = []
def __init__(self, database_url): self.database_url = database_url self.conn = phoenixdb.connect(database_url, autocommit=True)
#!/usr/bin/env python import phoenixdb with phoenixdb.connect('http://localhost:8765/', autocommit=True) as connection: with connection.cursor() as cursor: cursor.execute("DROP TABLE IF EXISTS test") cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.executemany("UPSERT INTO test VALUES (?, ?)", [[1, 'hello'], [2, 'world']]) cursor.execute("SELECT * FROM test ORDER BY id") for row in cursor: print row
# # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. # ############################################################################ import phoenixdb import phoenixdb.cursor import sys if __name__ == '__main__': pqs_port = sys.argv[1] database_url = 'http://localhost:' + str(pqs_port) + '/' print("CREATING PQS CONNECTION") conn = phoenixdb.connect(database_url, autocommit=True, auth="SPNEGO") cursor = conn.cursor() cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username VARCHAR)") cursor.execute("UPSERT INTO users VALUES (?, ?)", (1, 'admin')) cursor.execute("UPSERT INTO users VALUES (?, ?)", (2, 'user')) cursor.execute("SELECT * FROM users") print("RESULTS") print(cursor.fetchall())