def test_transaction(self): self.reopen(autocommit=False) with self.conn.cursor() as cursor: self.createTable( "test", "CREATE TABLE {table} (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'one']) cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), []) self.conn.commit() cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [[1, 'one']]) self.assertEqual(self.conn.autocommit, False) cursor.execute("UPSERT INTO test VALUES (?, ?)", [2, 'two']) self.conn.rollback() cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [[1, 'one']]) self.assertEqual(self.conn.autocommit, False) cursor.execute("UPSERT INTO test VALUES (?, ?)", [2, 'two']) # Since we expose the JDBC semantics, this is an implicit commit self.conn.autocommit = True cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [[1, 'one'], [2, 'two']])
def getMenubyID(id): cmd = "SELECT menu_id, menu_parent, menu_label, menu_name, menu_description, menu_active, menu_url, menu_config, menu_icon, menu_sort " \ "FROM FHIR247.UI_MENU WHERE menu_id = '" + id + "'" conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listMenu = cursor.fetchall() conn.close() if len(listMenu) > 0: MenuJSON = {} for data in listMenu: MenuJSON["id"] = data[0] MenuJSON["parent"] = data[1] MenuJSON["label"] = data[2] MenuJSON["name"] = data[3] MenuJSON["description"] = data[4] MenuJSON["active"] = data[5] MenuJSON["url"] = data[6] MenuJSON["config"] = data[7] MenuJSON["icon"] = data[8] MenuJSON["sort"] = data[9] result = {"status": 200, "data": MenuJSON} else: result = {"status": 404, "message": "Menu ID is not found."} return result
def tokenValidate(token): result = {} resultSecret = getApikeyApiSecretSuperAdmin(token) if resultSecret: result = {"status": 200, "message": "This is token SuperAdmin!"} else: #check from db connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT user_id, TO_CHAR(expiry_token, 'yyyy-MM-dd HH:mm:ss'), token FROM FHIR247.UI_USERS WHERE token = '"+ token +"' " cursor.execute(cmd) dataUser = cursor.fetchall() if len(dataUser) > 0: for data in dataUser: # userID = data[0] expiry = data[1] # token = data[2] if expiry > datetime.now().strftime('%Y-%m-%d %H:%M:%S'): result = {"status": 200, "message": "Token is valid!"} else: result = {"status": 403, "message": "Token is expired!"} else: result = {"status": 404, "message": "Token is not found!"} connDB.close() return result
def _check_dict_cursor(self, cursor): cursor.execute("DROP TABLE IF EXISTS test") cursor.execute( "CREATE TABLE test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [{'ID': 1, 'TEXT': 'text 1'}])
def query_join(conn, columns_list, table0, table1, join_column0, join_column1, join_type, table1_columns): with conn.cursor() as cursor: join_str = "" if join_type == "left": join_str = "left join" elif join_type == "right": join_str = "right join" elif join_type == "inner": join_str = "inner join" elif join_type == "full": join_str = "full join" query_sql = join_query_sql % (table0, join_str, table1, join_column0, join_column1) cursor.execute(query_sql) fetchall = cursor.fetchall() # 删除第一二行的ROW fetchall_list = [] table1_column_list = table1_columns.split("^") for fetchone in fetchall: size_index = len(columns_list) - len(table1_column_list) + 1 fetchone.pop(size_index) fetchone.pop(0) fetchall_list.append(fetchone) return fetchall_list
def _check_dict_cursor(self, cursor): self.createTable( "test", "CREATE TABLE {table} (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [{'ID': 1, 'TEXT': 'text 1'}])
def checkPrivilegeMenu(token, menu): resultSecret = getApikeyApiSecretSuperAdmin(token) if resultSecret: result = {"status": 200, "message": "This is token SuperAdmin!", "super_admin": True} else: #check from db connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT privilege_id, has_insert, has_update, has_delete, has_view FROM FHIR247.UI_PRIVILEGES p, " \ "FHIR247.UI_GROUPS g, FHIR247.UI_MENU m, FHIR247.UI_USERS u " \ "WHERE p.group_id = g.group_id AND p.menu_id = m.menu_id AND g.group_id = u.group_id AND " \ "u.token = '"+ token +"' AND m.menu_id = '"+ menu +"' " cursor.execute(cmd) dataPrivileges = cursor.fetchall() if len(dataPrivileges) > 0: for privilege in dataPrivileges: privilegeId = privilege[0] hasInsert = privilege[1] hasUpdate = privilege[2] hasDelete = privilege[3] hasView = privilege[4] result = {"status": 200, "privilegeId": privilegeId, "hasInsert": hasInsert, "hasUpdate": hasUpdate, "hasDelete": hasDelete, "hasView": hasView, "super_admin": False} else: result = {"status": 404, "message": "Permission denied, user privileges not found!"} connDB.close() return result
def getPrivilegeByID(id): cmd = "SELECT privilege_id, has_insert, has_update, has_delete, has_view, has_approval " \ "FROM FHIR247.UI_PRIVILEGES WHERE privilege_id = '" + id + "'" conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listPrivilege = cursor.fetchall() conn.close() if len(listPrivilege) > 0: PrivilegeJSON = {} for data in listPrivilege: PrivilegeJSON["id"] = data[0] PrivilegeJSON["hasInsert"] = data[1] PrivilegeJSON["hasUpdate"] = data[2] PrivilegeJSON["hasDelete"] = data[3] PrivilegeJSON["hasView"] = data[4] PrivilegeJSON["hasApproval"] = data[5] result = {"status": 200, "data": PrivilegeJSON} else: result = {"status": 404, "message": "Privilege ID is not found."} return result
def test_select_parameter(self): with self.conn.cursor() as cursor: self.createTable("test", "CREATE TABLE {table} (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.executemany("UPSERT INTO test VALUES (?, ?)", [[i, 'text {}'.format(i)] for i in range(10)]) with self.conn.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 get_fields(table: str, cursor): query = f"""select column_name from system.catalog where table_schem is NULL and table_name = '{table}' and column_name is not null order by ordinal_position """ cursor.execute(query, parameters=()) resp = cursor.fetchall() fields = [i for sublist in resp for i in sublist] if resp is not None else list() return fields
def query_dp_data(query_sql): phoenix_app = create_app(get_config('develop')) phoenix_app.app_context() database_url = phoenix_app.config['DATABASE_URL'] conn = phoenixdb.connect(database_url, autocommit=True) with conn.cursor() as cursor: cursor.execute(query_sql) fetchall = cursor.fetchall() conn.close() return fetchall
def query(cls, sql_str, params=None): conn = phoenixdb.connect(hbase_url, autocommit=True) cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) try: time_start = time.time() cursor.execute(sql_str, params) if time.time() - time_start > 30: HBaseHelper.clear_status_table(conn) except Exception as ex: HBaseHelper.clear_status_table(conn) raise ex return cursor.fetchall()
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 query(self): """ 查询函数,查询sql并将结果赋予self.data。 """ conn = phoenixdb.connect(HbaseMysqlConfig.HBASE_IP, autocommit=True) cursor = conn.cursor() cursor.execute(self.sql) result = cursor.fetchall() cursor.close() conn.close() self.data = result
def test_schema(self): with self.conn.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 self.createTable("test_schema.test", "CREATE TABLE {table} (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 generateNewToken(apikey, apisecret): connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT user_id, TO_CHAR(expiry_token, 'yyyy-MM-dd HH:mm:ss'), token FROM FHIR247.UI_USERS WHERE apikey = ? AND apisecret= ? " cursor.execute(cmd, (apikey, apisecret),) dataUser = cursor.fetchall() result = {} if len(dataUser) > 0: #check expiry token existing for data in dataUser: userID = data[0] expiry = data[1] token = data[2] if token != None: if expiry > datetime.now().strftime('%Y-%m-%d %H:%M:%S'): print("Token masih aktif") newToken = token expiryToken = expiry else: # set token 1 hours print("Token expired 1") newToken = generateUniqeID('T0K247') expiryTokenOneHours = datetime.now() + timedelta(hours=1) expiryToken = expiryTokenOneHours.strftime('%Y-%m-%d %H:%M:%S') # upsert cmd = "UPSERT INTO FHIR247.UI_USERS(user_id, token, expiry_token) SELECT user_id, '" + newToken + "', TO_DATE('" + expiryToken + "', 'yyyy-MM-dd HH:mm:ss') FROM FHIR247.UI_USERS WHERE user_id = '" + userID + "'" cursor.execute(cmd) else: #set token 1 hours print("Token expired 2") newToken = generateUniqeID('T0K247') expiryTokenOneHours = datetime.now() + timedelta(hours=1) expiryToken = expiryTokenOneHours.strftime('%Y-%m-%d %H:%M:%S') #upsert cmd = "UPSERT INTO FHIR247.UI_USERS(user_id, token, expiry_token) SELECT user_id, '"+ newToken +"', TO_DATE('"+ expiryToken +"', 'yyyy-MM-dd HH:mm:ss') FROM FHIR247.UI_USERS WHERE user_id = '"+ userID + "'" cursor.execute(cmd) result = {"status": 200, "token": newToken, "expiry": expiryToken} else: result = {"status": 200, "message": "Apikey and apisecret is not exist."} connDB.close() return result
def getUserByEmailOrUsername(username, email): cmd = "SELECT user_id, username, firstname, lastname, fullname, email," \ " TO_CHAR(last_login, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(last_activity, 'yyyy-MM-dd HH:mm:ss'), is_login, is_block, " \ " TO_CHAR(first_login, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(last_pass_change, 'yyyy-MM-dd HH:mm:ss'), is_active, " \ " TO_CHAR(created_date, 'yyyy-MM-dd HH:mm:ss'), created_by, TO_CHAR(updated_date, 'yyyy-MM-dd HH:mm:ss'), updated_by, is_deleted, " \ " apikey, apisecret, token, group_id, password " \ " FROM FHIR247.UI_USERS WHERE username = '******' OR email = '"+ email +"' ORDER BY user_id " conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listUser = cursor.fetchall() conn.close() if len(listUser) > 0: UserJSON = {} for data in listUser: UserJSON["id"] = data[0] UserJSON["username"] = data[1] UserJSON["firstname"] = data[2] UserJSON["lastname"] = data[3] UserJSON["fullname"] = data[4] UserJSON["email"] = data[5] UserJSON["lastLogin"] = data[6] UserJSON["lastActivity"] = data[7] UserJSON["isLogin"] = data[8] UserJSON["isBlock"] = data[9] UserJSON["firstLogin"] = data[10] UserJSON["lastPassChange"] = data[11] UserJSON["isActive"] = data[12] UserJSON["createdDate"] = data[13] UserJSON["createdBy"] = data[14] UserJSON["updatedDate"] = data[15] UserJSON["updatedBy"] = data[16] UserJSON["isDeleted"] = data[17] UserJSON["apiKey"] = data[18] UserJSON["apiSecret"] = data[19] UserJSON["token"] = data[20] UserJSON["groupId"] = data[21] UserJSON["password"] = data[22] result = {"status": 200, "data": UserJSON} else: result = {"status": 404, "message": "User is not found."} return result
def getPatientbyID(id): cmd = "SELECT PATIENT_ID FROM FHIR247.PATIENT WHERE PATIENT_ID = '" + id + "' ORDER BY PATIENT_ID " conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listPatient = cursor.fetchall() conn.close() if len(listPatient) > 0: result = {"status": 200} else: result = {"status": 404, "message": "Patient ID is not found."} return result
def query(self, sql, is_dict): try: # 判断是否需要返回结果为字典类型 if is_dict: cursor = self.conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) else: cursor = self.conn.cursor(cursor_factory=phoenixdb.cursor.Cursor) # 执行sql语句 cursor.execute(sql) # 查询结果 data = cursor.fetchall() # 关闭游标 cursor.close() return data except Exception as e: raise Exception('查询异常>>> {}'.format(e))
def pandas_read_phoenix(cursor, sql_statement: str, batch=10): cursor.execute(sql_statement) rows = cursor.fetchall() cols = list(rows[0].keys()) df = pd.DataFrame(columns=cols) df_rows = [] for row in rows: df_rows.append(row) if len(df_rows) == batch: df = df.append(df_rows) df_rows.clear() return df
def getPersonbyID(id): cmd = "SELECT PERSON_ID FROM FHIR247.PERSON WHERE PERSON_ID = '" + id + "' ORDER BY PERSON_ID " conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listPerson = cursor.fetchall() conn.close() if len(listPerson) > 0: result = {"status": 200} else: result = {"status": 404, "message": "Person ID is not found."} return result
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 easy_conn(): """ 基础操作phoenix :return: """ database_url = 'http://%s:8765/' % ZK_URL conn = phoenixdb.connect(database_url, autocommit=True) cursor = conn.cursor() cursor.execute("DROP TABLE users") cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username VARCHAR)") cursor.execute("UPSERT INTO users VALUES (?, ?)", (1, 'qjj')) cursor.execute("SELECT * FROM users") print(cursor.fetchall()) # 只能用在select之后,fetch之后清空cursor的内容,再fetch结果为空 # ----------------------------------------------- cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) # DictCursor :A cursor which returns results as a dictionary cursor.execute("SELECT * FROM users WHERE id=1") print(cursor.fetchone()['USERNAME'])
def getMenuByName(name): cmd = "SELECT menu_id, menu_parent, menu_label, menu_name, menu_description, menu_active, menu_url, menu_config, menu_icon, menu_sort " \ "FROM FHIR247.UI_MENU WHERE menu_name = '" + name + "'" conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listMenu = cursor.fetchall() conn.close() if len(listMenu) > 0: result = {"status": 403, "message": "Menu name already exist."} else: result = {"status": 200, "message": "Menu name is ready to use."} return result
def usernameValidate(username): result = {} #check from db connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT user_id FROM FHIR247.UI_USERS WHERE username = '******' " cursor.execute(cmd) dataUser = cursor.fetchall() if len(dataUser) > 0: result = {"status": 403, "message": "Username already exist!"} else: result = {"status": 200, "message": "Username ready to use."} connDB.close() return result
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 test_param_number_mismatch(self): self.createTable("phoenixdb_test_param_number", "CREATE TABLE {table} (id INTEGER PRIMARY KEY, username VARCHAR, name VARCHAR)") with self.conn.cursor() as cursor: cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?, ?)", (123, 'John Doe', 'Doe')) cursor.execute("SELECT * FROM phoenixdb_test_param_number") self.assertEqual(cursor.fetchall(), [ [123, 'John Doe', 'Doe'] ]) with self.assertRaises(ProgrammingError) as cm: cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?)", (123, 'John Doe', 'admin')) self.assertEqual("Number of placeholders (?) must match number of parameters." " Number of placeholders: 2. Number of parameters: 3", cm.exception.message) with self.assertRaises(ProgrammingError) as cm: cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?, ?)", (123, 'John Doe', 'admin', 'asd')) self.assertEqual("Number of placeholders (?) must match number of parameters." " Number of placeholders: 3. Number of parameters: 4", cm.exception.message) with self.assertRaises(ProgrammingError) as cm: cursor.execute("UPSERT INTO phoenixdb_test_param_number VALUES (?, ?, ?)", (123, 'John Doe')) self.assertEqual("Number of placeholders (?) must match number of parameters." " Number of placeholders: 3. Number of parameters: 2", cm.exception.message)
def getApikeyApiSecretSuperAdmin(token): # check from db connDB = phoenixConn() cursor = connDB.cursor() cmd = "SELECT user_id, apikey, apisecret FROM FHIR247.UI_USERS WHERE token = '" + token + "' " cursor.execute(cmd) dataUser = cursor.fetchall() connDB.close() if len(dataUser) > 0: for data in dataUser: # userID = data[0] apiKey = data[1] apiSecret = data[2] if apiKey == '64cb156db93458ac469e8a3562b7892fa0d08597' and apiSecret == 'e5101c5b7dac13db925b99a53d174bbdc648b0eb': return True else: return False else: return False
def isdataready(date, source): """ 判断某个时间的数据是否全部到达。 :param date: 判断的日期。(可为小时或天) :param source: 数据来源 :return: 数据是否已经到达 """ date = str(date) + '23' if len(str(date)) == 8 else str(date) conn = phoenixdb.connect(HbaseMysqlConfig.HBASE_IP, autocommit=True) cursor = conn.cursor() if source == 'cctv5': table = HbaseMysqlConfig.CCTV5_HBASE_TABLE threshold = 20000 elif source == 'finance': table = HbaseMysqlConfig.FINANCE_HBASE_TABLE threshold = 300 elif source == 'children': table = HbaseMysqlConfig.CHILDREN_HBASE_TABLE threshold = 50 elif source == 'music': table = HbaseMysqlConfig.MUSIC_HBASE_TABLE threshold = 100 else: table = '' threshold = 100 sql = "select count(*) from {table} where ymdh={ymdh}".format(table=table, ymdh=date) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() if int(result[0][0]) > threshold: return True else: return False
def getGroupbyID(id): cmd = "SELECT group_id, name, description FROM FHIR247.UI_GROUPS WHERE group_id = '" + id + "' ORDER BY group_id " conn = phoenixConn() cursor = conn.cursor() cursor.execute(cmd) listGroup = cursor.fetchall() conn.close() if len(listGroup) > 0: GroupJSON = {} for data in listGroup: GroupJSON["id"] = data[0] GroupJSON["name"] = data[1] GroupJSON["description"] = data[2] result = {"status": 200, "data": GroupJSON} else: result = {"status": 404, "message": "Group ID is not found."} return result
def isdataready(date): """ 判断某个时间的数据是否全部到达。 :param date: 判断的日期。(可为小时或天) :return: 数据是否已经到达 """ date = str(date) + '23' if len(str(date)) == 8 else str(date) conn = phoenixdb.connect(HbaseMysqlConfig.HBASE_IP, autocommit=True) cursor = conn.cursor() sql = "select count(*) from {table} where ymdh={ymdh}".format( table=HbaseMysqlConfig.HBASE_TABLE, ymdh=date) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() if int(result[0][0]) > 500: return True else: return False
# Use UPSERT import phoenixdb.cursor conn = phoenixdb.connect('http://localhost:8765', autocommit=True) cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor) table = 'USERS' id_column = 'id' username_column = 'username' cursor.execute(f'DROP TABLE IF EXISTS {table}') cursor.execute(f'CREATE TABLE {table} ({id_column} INTEGER PRIMARY KEY, {username_column} VARCHAR)') cursor.execute(f"UPSERT INTO {table} ({id_column}, {username_column}) VALUES (1, 'admin')") cursor.execute(f"UPSERT INTO {table} ({id_column}, {username_column}) VALUES (2, 'guest')") cursor.execute(f'SELECT * FROM {table}', parameters=()) # "parameters" can't be None result = cursor.fetchall() assert str(result) == "[{'ID': 1, 'USERNAME': '******'}, {'ID': 2, 'USERNAME': '******'}]" cursor.close() conn.close()
def _check_dict_cursor(self, cursor): cursor.execute("DROP TABLE IF EXISTS test") cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, text VARCHAR)") cursor.execute("UPSERT INTO test VALUES (?, ?)", [1, 'text 1']) cursor.execute("SELECT * FROM test ORDER BY id") self.assertEqual(cursor.fetchall(), [{'ID': 1, 'TEXT': 'text 1'}])
# # 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())