def test_auth_sha265_memory(self): sess = mysqlx.get_session(self.connect_kwargs) sess.sql("CREATE USER 'caching'@'%' IDENTIFIED WITH " "caching_sha2_password BY 'caching'").execute() config = { "user": "******", "password": "******", "host": self.connect_kwargs["host"], "port": self.connect_kwargs["port"] } # Session creation is not possible with SSL disabled config["ssl-mode"] = mysqlx.SSLMode.DISABLED self.assertRaises(InterfaceError, mysqlx.get_session, config) config["auth"] = mysqlx.Auth.SHA256_MEMORY self.assertRaises(InterfaceError, mysqlx.get_session, config) # Session creation is possible with SSL enabled config["ssl-mode"] = mysqlx.SSLMode.REQUIRED config["auth"] = mysqlx.Auth.PLAIN mysqlx.get_session(config) # Disable SSL config["ssl-mode"] = mysqlx.SSLMode.DISABLED # Password is in cache will, session creation is possible config["auth"] = mysqlx.Auth.SHA256_MEMORY mysqlx.get_session(config) sess.sql("DROP USER 'caching'@'%'").execute() sess.close()
def test_bind_to_default_shard(self): try: # Getting a NodeSession to the default shard sess = mysqlx.get_session(self.connect_kwargs) nsess = sess.bind_to_default_shard() self.assertEqual(sess._settings, nsess._settings) # Close XSession and all dependent NodeSessions sess.close() self.assertFalse(nsess.is_open()) # Connection error on XSession sess = mysqlx.get_session(self.connect_kwargs) nsess_a = sess.bind_to_default_shard() nsess_b = sess.bind_to_default_shard() tests.MYSQL_SERVERS[0].stop() tests.MYSQL_SERVERS[0].wait_down() self.assertRaises(mysqlx.errors.InterfaceError, sess.get_default_schema().exists_in_database) self.assertFalse(sess.is_open()) self.assertFalse(nsess_a.is_open()) self.assertFalse(nsess_b.is_open()) tests.MYSQL_SERVERS[0].start() tests.MYSQL_SERVERS[0].wait_up() # Connection error on dependent NodeSession sess = mysqlx.get_session(self.connect_kwargs) nsess_a = sess.bind_to_default_shard() nsess_b = sess.bind_to_default_shard() tests.MYSQL_SERVERS[0].stop() tests.MYSQL_SERVERS[0].wait_down() self.assertRaises(mysqlx.errors.InterfaceError, nsess_a.sql("SELECT 1").execute) self.assertFalse(nsess_a.is_open()) self.assertTrue(nsess_b.is_open()) self.assertTrue(sess.is_open()) tests.MYSQL_SERVERS[0].start() tests.MYSQL_SERVERS[0].wait_up() # Getting a NodeSession a shard (connect error) sess = mysqlx.get_session(self.connect_kwargs) tests.MYSQL_SERVERS[0].stop() tests.MYSQL_SERVERS[0].wait_down() self.assertRaises(mysqlx.errors.InterfaceError, sess.bind_to_default_shard) tests.MYSQL_SERVERS[0].start() tests.MYSQL_SERVERS[0].wait_up() finally: if not tests.MYSQL_SERVERS[0].check_running(): tests.MYSQL_SERVERS[0].start() tests.MYSQL_SERVERS[0].wait_up()
def test_ssl_connection(self): config = {} config.update(self.connect_kwargs) # Secure by default session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() config["ssl-key"] = tests.SSL_KEY self.assertRaises(mysqlx.errors.InterfaceError, mysqlx.get_session, config) # Connection with ssl parameters config["ssl-ca"] = tests.SSL_CA config["ssl-cert"] = tests.SSL_CERT session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() ssl_ca="{0}{1}".format(config["ssl-ca"][0], quote_plus(config["ssl-ca"][1:])) ssl_key="{0}{1}".format(config["ssl-key"][0], quote_plus(config["ssl-key"][1:])) ssl_cert="{0}{1}".format(config["ssl-ca"][0], quote_plus(config["ssl-cert"][1:])) uri = build_uri(user=config["user"], password=config["password"], host=config["host"], ssl_ca=ssl_ca, ssl_cert=ssl_cert, ssl_key=ssl_key) session = mysqlx.get_session(uri) ssl_ca = "({0})".format(config["ssl-ca"]) ssl_cert = "({0})".format(config["ssl-cert"]) ssl_key = "({0})".format(config["ssl-key"]) uri = build_uri(user=config["user"], password=config["password"], host=config["host"], ssl_ca=ssl_ca, ssl_cert=ssl_cert, ssl_key=ssl_key) session = mysqlx.get_session(uri)
def test_ssl_connection(self): config = {} config.update(self.connect_kwargs) # Secure by default session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() config["ssl-key"] = tests.SSL_KEY self.assertRaises(mysqlx.errors.InterfaceError, mysqlx.get_session, config) # Connection with ssl parameters config["ssl-ca"] = tests.SSL_CA config["ssl-cert"] = tests.SSL_CERT session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() uri = ("mysqlx://{0}:{1}@{2}?ssl-ca={3}&ssl-cert={4}&ssl-key={5}" "".format(config["user"], config["password"], config["host"], quote(config["ssl-ca"]), quote(config["ssl-cert"]), quote(config["ssl-key"]))) session = mysqlx.get_session(uri) uri = ("mysqlx://{0}:{1}@{2}?ssl-ca=({3})&ssl-cert=({4})&ssl-key=({5})" "".format(config["user"], config["password"], config["host"], config["ssl-ca"], config["ssl-cert"], config["ssl-key"])) session = mysqlx.get_session(uri)
def test_connection_uri(self): uri = build_uri(user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], host=self.connect_kwargs["host"], port=self.connect_kwargs["port"], schema=self.connect_kwargs["schema"], use_pure=False) session = mysqlx.get_session(uri) self.assertIsInstance(session, mysqlx.Session) # Test URI parser function for uri, res in _URI_TEST_RESULTS: try: settings = mysqlx._get_connection_settings(uri) self.assertEqual(res, settings) except mysqlx.Error: self.assertEqual(res, None) # Test URI parser function for uri, res in _ROUTER_LIST_RESULTS: try: settings = mysqlx._get_connection_settings(uri) self.assertEqual(res, settings) except mysqlx.Error: self.assertEqual(res, None)
def setUp(self): settings = tests.get_mysqlx_config() self.schema_name = settings["schema"] self.host = settings["host"] self.user = "******" self.password = "******" self.hosts = "127.0.0.1", "localhost" try: self.session = mysqlx.get_session(settings) except mysqlx.Error as err: self.fail("{0}".format(err)) for host in self.hosts: self.session.sql(DROP_USER.format(user=self.user, host=host)).execute() for host in self.hosts: self.session.sql( CREATE_USER.format(user=self.user, host=host, password=self.password)).execute() # Grant all to new user on database self.session.sql( GRANT_USER.format(database=self.schema_name, user=self.user, host=host, password=self.password)).execute()
def setUp(self): self.connect_kwargs = tests.get_mysqlx_config() self.schema_name = self.connect_kwargs["schema"] try: self.session = mysqlx.get_session(self.connect_kwargs) except mysqlx.Error as err: self.fail("{0}".format(err))
def test_mysqlx_socket(self): # Connect with unix socket uri = "mysqlx://{user}:{password}@({socket})".format( user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], socket=self.connect_kwargs["socket"]) session = mysqlx.get_session(uri)
def test_disabled_x_protocol(self): session = mysqlx.get_session(self.connect_kwargs) res = session.sql("SHOW VARIABLES WHERE Variable_name = 'port'") \ .execute().fetch_all() settings = self.connect_kwargs.copy() settings["port"] = res[0][1] # Lets use the MySQL classic port session.close() self.assertRaises(ProgrammingError, mysqlx.get_session, settings)
def test_auth(self): sess = mysqlx.get_session(self.connect_kwargs) sess.sql("CREATE USER 'native'@'%' IDENTIFIED WITH " "mysql_native_password BY 'test'").execute() sess.sql("CREATE USER 'sha256'@'%' IDENTIFIED WITH " "sha256_password BY 'sha256'").execute() config = {'host': self.connect_kwargs['host'], 'port': self.connect_kwargs['port']} config['user'] = '******' config['password'] = '******' config['auth'] = 'plain' mysqlx.get_session(config) config['auth'] = 'mysql41' mysqlx.get_session(config) config['user'] = '******' config['password'] = '******' if tests.MYSQL_VERSION >= (8, 0, 1): config['auth'] = 'plain' mysqlx.get_session(config) config['auth'] = 'mysql41' self.assertRaises(InterfaceError, mysqlx.get_session, config) sess.sql("DROP USER 'native'@'%'").execute() sess.sql("DROP USER 'sha256'@'%'").execute() sess.close()
def test_get_default_schema(self): schema = self.session.get_default_schema() self.assertTrue(schema, mysqlx.Schema) self.assertEqual(schema.get_name(), self.connect_kwargs["schema"]) # Test without default schema configured at connect time settings = self.connect_kwargs.copy() settings["schema"] = None session = mysqlx.get_session(settings) self.assertRaises(mysqlx.ProgrammingError, session.get_default_schema) session.close()
def test_use_pure(self): settings = self.connect_kwargs.copy() settings["use-pure"] = False session = mysqlx.get_session(settings) self.assertFalse(session.use_pure) self.assertEqual(Protobuf.mysqlxpb.__name__, "_mysqlxpb") session.use_pure = True self.assertTrue(session.use_pure) self.assertEqual(Protobuf.mysqlxpb.__name__, "_mysqlxpb_pure") # 'use_pure' should be a bool type self.assertRaises(ProgrammingError, setattr, session, "use_pure", -1) session.close()
def test_get_session(self): """Test get_session() opens new connections.""" # Auxiliary session to query server old_session = mysqlx.get_session(self.connect_kwargs.copy()) # Setup a client to get sessions from settings = self.connect_kwargs.copy() pooling_dict = {"enabled": True, "max_idle_time":3000} cnx_options = {"pooling": pooling_dict} settings["user"] = self.users[0][0] settings["password"] = self.users[0][1] settings["host"] = self.host settings.pop("socket") client = mysqlx.get_client(settings, cnx_options) # Get 10 sessions, client will start 10 connections total_connections = 10 sessions = [] for _ in range(0, total_connections): session = client.get_session() self.assertTrue(isinstance(session, mysqlx.connection.Session)) sessions.append(session) sleep(0.5) # Verify the number of connections open in the server connections = get_current_connections(old_session) self.assertEqual(len(connections[self.users[0][0]]), total_connections) # Verify that clossing the session returns the connection # to the pool instead of being closed sessions[5].close() sessions[9].close() sleep(0.5) connections = get_current_connections(old_session) self.assertTrue(len(connections[self.users[0][0]]) >= (total_connections - 2)) connections = get_current_connections(old_session) open_connections = connections.get("unauthenticated user", []) if tests.MYSQL_VERSION < (8, 0, 16): # Send reset message requires the user to re-authentificate # the connection user stays in unauthenticated user self.assertEqual(len(open_connections), 2) else: self.assertEqual(len(open_connections), 0) # Connections must be closed when client.close() is invoked # check len(pool) == total_connections client.close() sleep(3) # Verify the connections on the pool are closed connections = get_current_connections(old_session) open_connections = connections.get("self.users[0][0]", 0) self.assertEqual(open_connections, 0)
def test_mysqlx_socket(self): # Connect with unix socket uri = "mysqlx://{user}:{password}@({socket})".format( user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], socket=self.connect_kwargs["socket"]) session = mysqlx.get_session(uri) # No SSL with Unix Sockets res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("OFF", res[0][1]) session.close() # Socket parsing tests conn = mysqlx._get_connection_settings("root:@(/path/to/sock)") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@(/path/to/sock)/schema") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"]) conn = mysqlx._get_connection_settings("root:@/path%2Fto%2Fsock") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@/path%2Fto%2Fsock/schema") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"]) conn = mysqlx._get_connection_settings("root:@.%2Fpath%2Fto%2Fsock") self.assertEqual("./path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@.%2Fpath%2Fto%2Fsock" "/schema") self.assertEqual("./path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"]) conn = mysqlx._get_connection_settings("root:@..%2Fpath%2Fto%2Fsock") self.assertEqual("../path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@..%2Fpath%2Fto%2Fsock" "/schema") self.assertEqual("../path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"])
def test_connection_uri(self): uri = ("mysqlx://{user}:{password}@{host}:{port}/{schema}" "".format(user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], host=self.connect_kwargs["host"], port=self.connect_kwargs["port"], schema=self.connect_kwargs["schema"])) session = mysqlx.get_session(uri) self.assertIsInstance(session, mysqlx.XSession) # Test URI parser function for uri, res in _ROUTER_LIST_RESULTS: try: settings = mysqlx._get_connection_settings(uri) self.assertEqual(res, settings) except mysqlx.Error: self.assertEqual(res, None)
def test_mysqlx_socket(self): # Connect with unix socket uri = "mysqlx://{user}:{password}@({socket})".format( user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], socket=self.connect_kwargs["socket"]) session = mysqlx.get_session(uri) conn = mysqlx._get_connection_settings("root:@(/path/to/sock)") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@(/path/to/sock)/schema") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"]) conn = mysqlx._get_connection_settings("root:@/path%2Fto%2Fsock") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@/path%2Fto%2Fsock/schema") self.assertEqual("/path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"]) conn = mysqlx._get_connection_settings("root:@.%2Fpath%2Fto%2Fsock") self.assertEqual("./path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@.%2Fpath%2Fto%2Fsock" "/schema") self.assertEqual("./path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"]) conn = mysqlx._get_connection_settings("root:@..%2Fpath%2Fto%2Fsock") self.assertEqual("../path/to/sock", conn["socket"]) self.assertEqual("", conn["schema"]) conn = mysqlx._get_connection_settings("root:@..%2Fpath%2Fto%2Fsock" "/schema") self.assertEqual("../path/to/sock", conn["socket"]) self.assertEqual("schema", conn["schema"])
def setUp(self): self.connect_kwargs = tests.get_mysqlx_config() self.schema_name = self.connect_kwargs["schema"] self.host = self.connect_kwargs['host'] try: self.session = mysqlx.get_session(self.connect_kwargs) except mysqlx.Error as err: self.fail("{0}".format(err)) self.users = [("client_user", "passclient"), ("max_size", "max_pass")] for user, password in self.users: self.session.sql(DROP_USER.format(user=user, host=self.host)) \ .execute() self.session.sql(CREATE_USER.format(user=user, host=self.host, password=password)).execute() # Grant all to new user on database self.session.sql(GRANT_USER.format(database=self.schema_name, user=user, host=self.host)) \ .execute()
import mysqlx except Exception: from mysqlsh import mysqlx # Get user id and password userid = input("User Id: ") passwd = getpass("Password: ") user_info = { 'host': 'localhost', 'port': 33060, 'user': userid, 'password': passwd, } # Connect to the database garage_v1 my_session = mysqlx.get_session(user_info) # Create the schema for garage_v2 my_session.drop_schema('garage_v2') garage_v2 = my_session.create_schema('garage_v2') # Create the collections cabinets = garage_v2.create_collection('cabinets') organizers = garage_v2.create_collection('organizers') shelving_units = garage_v2.create_collection('shelving_units') tools = garage_v2.create_collection('tools') toolchests = garage_v2.create_collection('toolchests') locations = garage_v2.create_collection('locations') workbenches = garage_v2.create_collection('workbenches') vendors = garage_v2.create_collection('vendors') # Show the collections print(garage_v2.get_collections())
# Import the MySQL X module import mysqlx import getpass # Get a session with a URI mysqlx_session = mysqlx.get_session("root:wrongpassworddude!@localhost:33060") # Check the connection if not mysqlx_session.is_open(): print("Connection failed!") exit(1) # Get the animals schema. schema = mysqlx_session.get_schema("animals") # Try to create the table using a SQL string. It should throw an # error that it already exists. res = mysqlx_session.sql("CREATE TABLE animals.pets_sql (" "`id` int auto_increment primary key, " "`name` char(20), " "`age` int, " "`breed` char(20), " "`type` char(12))").execute() # Close the connection mysqlx_session.close()
import mysqlx mySession try: # Connect to server on localhost mySession = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'dbUser': '******', 'dbPassword': '******' }) except Exception, err: print 'The database session could not be opened: %s' % str(err) try: myDb = mySession.get_schema('test') # Use the collection 'my_collection' myColl = myDb.get_collection('my_collection') # Find a document myDoc = myColl.find('name like :param').limit(1).bind('param', 'S%').execute() # Print document print myDoc.first() except Exception, err: print 'The following error occurred: %s' % str(err) finally:
def test___init__(self): bad_config = { "host": "bad_host", "port": "", "username": "******", "password": "" } self.assertRaises(InterfaceError, mysqlx.Session, bad_config) host = self.connect_kwargs["host"] port = self.connect_kwargs["port"] user = self.connect_kwargs["user"] password = self.connect_kwargs["password"] # Session to a farm using one of many routers (prios) # Loop during connect because of network error (succeed) routers = [{ "host": "bad_host", "priority": 100 }, { "host": host, "port": port, "priority": 98 }] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # Session to a farm using one of many routers (incomplete prios) routers = [{ "host": "bad_host", "priority": 100 }, { "host": host, "port": port }] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except ProgrammingError as err: self.assertEqual(4000, err.errno) # Session to a farm using invalid priorities (out of range) routers = [{ "host": "bad_host", "priority": 100 }, { "host": host, "port": port, "priority": 101 }] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except ProgrammingError as err: self.assertEqual(4007, err.errno) # Establish an Session to a farm using one of many routers (no prios) routers = [{"host": "bad_host"}, {"host": host, "port": port}] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # Break loop during connect (non-network error) uri = build_uri(user=user, password="******", routers=routers) self.assertRaises(InterfaceError, mysqlx.get_session, uri) # Break loop during connect (none left) uri = "mysqlx://{0}:{1}@[bad_host, another_bad_host]".format( user, password) self.assertRaises(InterfaceError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except InterfaceError as err: self.assertEqual(4001, err.errno) # Invalid option with URI uri = "mysqlx://{0}:{1}@{2}:{3}?invalid=option" \ "".format(user, password, host, port) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) # Invalid option with dict config = { "user": user, "password": password, "host": host, "port": port, "invalid": "option" } self.assertRaises(ProgrammingError, mysqlx.get_session, config) # Invalid option with kwargs self.assertRaises(ProgrammingError, mysqlx.get_session, **config) usr_file = os.path.join(os.getcwd(), "mysqlx_usr_sessions.json") sys_file = os.path.join(os.getcwd(), "mysqlx_sys_sessions.json") temp = mysqlx.sessions try: configs = mysqlx.config.SessionConfigManager() configs.set_persistence_handler( mysqlx.config.PersistenceHandler(sys_file, usr_file)) mysqlx.sessions = configs config = configs.save( "myserver1", "{0}:{1}@{2}:{3}".format(self.connect_kwargs["user"], self.connect_kwargs["password"], self.connect_kwargs["host"], self.connect_kwargs["port"]), {'foo': 'baz'}) mysqlx.get_session(config, self.connect_kwargs["password"]) mysqlx.get_session({"session_name": "myserver1"}, self.connect_kwargs["password"]) except: self.fail("Failed to create XSession with saved configuration.") finally: mysqlx.sessions = temp if os.path.exists(usr_file): os.remove(usr_file) if os.path.exists(sys_file): os.remove(sys_file) # SocketSteam.is_socket session = mysqlx.get_session(user=user, password=password, host=host, port=port) self.assertFalse(session._connection.stream.is_socket)
def test_ssl_connection(self): config = {} config.update(self.connect_kwargs) socket = config.pop("socket") # Secure by default session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() # Error on setting Client key without Client Certificate config["ssl-key"] = tests.SSL_KEY self.assertRaises(InterfaceError, mysqlx.get_session, config) # Error on settings CRL without setting CA Certificate config["ssl-crl"] = "/dummy/path" self.assertRaises(InterfaceError, mysqlx.get_session, config) config.pop("ssl-crl") # Error on setting SSL Mode to disabled with any SSL option config["ssl-mode"] = "disabled" self.assertRaises(InterfaceError, mysqlx.get_session, config) # Error on setting SSL Mode to verify_* without ssl_ca config["ssl-mode"] = "verify_ca" self.assertRaises(InterfaceError, mysqlx.get_session, config) config["ssl-mode"] = "verify_identity" self.assertRaises(InterfaceError, mysqlx.get_session, config) # Error on SSL Mode set to required with CA set config["ssl-ca"] = tests.SSL_CA config["ssl-cert"] = tests.SSL_CERT config["ssl-mode"] = "required" self.assertRaises(InterfaceError, mysqlx.get_session, config) # Connection with ssl parameters config["ssl-mode"] = "verify_identity" session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement(session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() # Error if ssl-mode=disabled and ssl_* set extra = [("ssl_mode", "disabled"), ("ssl_ca", "({0})".format(tests.SSL_CA))] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) self.assertRaises(InterfaceError, mysqlx.get_session, uri) # Error if invalid ssl-mode extra = [("ssl_mode", "invalid")] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) self.assertRaises(InterfaceError, mysqlx.get_session, uri) # Parsing SSL Certificates extra = [("ssl_mode", "verify_ca"), ("ssl_ca", file_uri(tests.SSL_CA, False)), ("ssl_key", file_uri(tests.SSL_KEY, False)), ("ssl_cert", file_uri(tests.SSL_CERT, False))] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) session = mysqlx.get_session(uri) extra = [("ssl_mode", "verify_ca"), ("ssl_ca", file_uri(tests.SSL_CA)), ("ssl_key", file_uri(tests.SSL_KEY)), ("ssl_cert", file_uri(tests.SSL_CERT))] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) session = mysqlx.get_session(uri)
def test_connect_timeout(self): config = self.connect_kwargs.copy() # 0 ms disables timouts on socket connections config["connect-timeout"] = 0 session = mysqlx.get_session(config) session.close() # 10000 ms should be time enough to connect config["connect-timeout"] = 10000 session = mysqlx.get_session(config) session.close() # Use connect timeout in URI session = mysqlx.get_session(build_uri(**config)) session.close() # Timeout for an unreachable host # https://en.wikipedia.org/wiki/IPv4#Special-use_addresses config["host"] = "198.51.100.255" config["connect-timeout"] = 2000 self.assertRaises(mysqlx.TimeoutError, mysqlx.get_session, config) # Multi-host scenarios # Connect to a secondary host if the primary fails routers = [{ "host": "198.51.100.255", "port": config["port"], "priority": 100 }, { "host": "127.0.0.1", "port": config["port"], "priority": 90 }] uri = build_uri(user=config["user"], password=config["password"], connect_timeout=2000, routers=routers) session = mysqlx.get_session(uri) session.close() # Fail to connect to all hosts routers = [{ "host": "198.51.100.255", "port": config["port"], "priority": 100 }, { "host": "192.0.2.255", "port": config["port"], "priority": 90 }] uri = build_uri(user=config["user"], password=config["password"], connect_timeout=2000, routers=routers) try: mysqlx.get_session(uri) self.fail("It should not connect to any unreachable host") except mysqlx.TimeoutError as err: self.assertEqual( err.msg, "All server connection attempts were aborted. " "Timeout of 2000 ms was exceeded for each " "selected server") # Trying to establish a connection with a wrong password should not # wait for timeout config["host"] = "127.0.0.1" config["password"] = "******" config["connect-timeout"] = 2000 time_start = time.time() self.assertRaises(InterfaceError, mysqlx.get_session, config) time_elapsed = time.time() - time_start session.close() if time_elapsed >= config["connect-timeout"]: self.fail("Trying to establish a connection with a wrong password " "should not wait for timeout") # The connection timeout value must be a positive integer config["connect-timeout"] = -1 self.assertRaises(TypeError, mysqlx.get_session, config) config["connect-timeout"] = 10.0983 self.assertRaises(TypeError, mysqlx.get_session, config) config["connect-timeout"] = "abc" self.assertRaises(TypeError, mysqlx.get_session, config)
def test___init__(self): bad_config = { "host": "bad_host", "port": "", "username": "******", "password": "" } self.assertRaises(TypeError, mysqlx.XSession, bad_config) host = self.connect_kwargs["host"] port = self.connect_kwargs["port"] user = self.connect_kwargs["user"] password = self.connect_kwargs["password"] # XSession to a farm using one of many routers (prios) # Loop during connect because of network error (succeed) uri = ("mysqlx://{0}:{1}@[(address=bad_host, priority=100)," "(address={2}:{3}, priority=98)]" "".format(user, password, host, port)) session = mysqlx.get_session(uri) session.close() # XSession to a farm using one of many routers (incomplete prios) uri = ("mysqlx://{0}:{1}@[(address=bad_host, priority=100), {2}:{3}]" "".format(user, password, host, port)) self.assertRaises(mysqlx.errors.ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except mysqlx.errors.ProgrammingError as err: self.assertEqual(4000, err.errno) # XSession to a farm using invalid priorities (out of range) uri = ("mysqlx://{0}:{1}@[(address=bad_host, priority=100), " "(address={2}:{3}, priority=101)]" "".format(user, password, host, port)) self.assertRaises(mysqlx.errors.ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except mysqlx.errors.ProgrammingError as err: self.assertEqual(4007, err.errno) # Establish an XSession to a farm using one of many routers (no prios) uri = ("mysqlx://{0}:{1}@[bad_host, {2}:{3}]" "".format(user, password, host, port)) session = mysqlx.get_session(uri) session.close() # Break loop during connect (non-network error) uri = ("mysqlx://{0}:{1}@[bad_host, {2}:{3}]" "".format(user, "bad_pass", host, port)) self.assertRaises(mysqlx.errors.InterfaceError, mysqlx.get_session, uri) # Break loop during connect (none left) uri = "mysqlx://{0}:{1}@[bad_host, another_bad_host]" self.assertRaises(mysqlx.errors.InterfaceError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except mysqlx.errors.InterfaceError as err: self.assertEqual(4001, err.errno)
def test_ssl_connection(self): config = {} config.update(self.connect_kwargs) # Secure by default session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() config["ssl-key"] = tests.SSL_KEY self.assertRaises(mysqlx.errors.InterfaceError, mysqlx.get_session, config) # Connection with ssl parameters config["ssl-ca"] = tests.SSL_CA config["ssl-cert"] = tests.SSL_CERT session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() ssl_ca = "{0}{1}".format(config["ssl-ca"][0], quote_plus(config["ssl-ca"][1:])) ssl_key = "{0}{1}".format(config["ssl-key"][0], quote_plus(config["ssl-key"][1:])) ssl_cert = "{0}{1}".format(config["ssl-ca"][0], quote_plus(config["ssl-cert"][1:])) uri = build_uri(user=config["user"], password=config["password"], host=config["host"], ssl_ca=ssl_ca, ssl_cert=ssl_cert, ssl_key=ssl_key) session = mysqlx.get_session(uri) ssl_ca = "({0})".format(config["ssl-ca"]) ssl_cert = "({0})".format(config["ssl-cert"]) ssl_key = "({0})".format(config["ssl-key"]) uri = build_uri(user=config["user"], password=config["password"], host=config["host"], ssl_ca=ssl_ca, ssl_cert=ssl_cert, ssl_key=ssl_key) session = mysqlx.get_session(uri)
# Assumptions: ensure_schema_does_not_exist is available # Assumes __uripwd is defined as <user>:<pwd>@<host>:<plugin_port> # validateMemer and validateNotMember are defined on the setup script import mysqlx #@ Session: validating members mySession = mysqlx.get_session(__uripwd) all_members = dir(mySession) # Remove the python built in members sessionMembers = [] for member in all_members: if not member.startswith('__'): sessionMembers.append(member) print sessionMembers validateMember(sessionMembers, 'close') validateMember(sessionMembers, 'create_schema') validateMember(sessionMembers, 'get_default_schema') validateMember(sessionMembers, 'get_schema') validateMember(sessionMembers, 'get_schemas') validateMember(sessionMembers, 'get_uri') validateMember(sessionMembers, 'set_fetch_warnings') validateMember(sessionMembers, 'default_schema') validateMember(sessionMembers, 'uri') #@ Session: validate dynamic members for system schemas validateNotMember(sessionMembers, 'mysql') validateNotMember(sessionMembers, 'information_schema')
import mysqlx # Connect to server on localhost session = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******' }) schema = session.get_schema('etudiants') # Use the collection 'my_collection' collection = schema.get_collection('import_programs') # Specify which document to find with Collection.find() result = collection.find().execute() # Print document docs = result.fetch_all() print(docs) session.close()
# Declarations TEST_SCHEMA = "rolodex" TEST_COL = "contacts" # Get user information print("Please enter the connection information.") user = raw_input("Username: "******"Password: "******"Hostname [localhost]: ") or 'localhost' port = raw_input("Port [33060]: ") or '33060' # Get a session object using a dictionary of terms mysqlx_session = mysqlx.get_session({ 'host': host, 'port': port, 'user': user, 'password': passwd }) # Check to see that the session is open. If not, quit. if not mysqlx_session.is_open(): exit(1) # Get the schema and create it if it doesn't exist schema = mysqlx_session.get_schema(TEST_SCHEMA) if not schema.exists_in_database(): schema = mysqlx_session.create_schema(TEST_SCHEMA) # Create a collection or use it if it already exists contacts = schema.create_collection(TEST_COL)
def tearDown(self): session = mysqlx.get_session(tests.get_mysqlx_config()) for host in self.hosts: session.sql(DROP_USER.format(user=self.user, host=host)).execute()
exports = [] for member in all_members: if not member.startswith('__'): exports.append(member) print 'Exported Items:', len(exports) print 'get_session:', type(mysqlx.get_session), '\n' print 'get_node_session:', type(mysqlx.get_node_session), '\n' print 'expr:', type(mysqlx.expr), '\n' print 'dateValue:', type(mysqlx.date_value), '\n' print 'Type:', mysqlx.Type, '\n' print 'IndexType:', mysqlx.IndexType, '\n' #@ mysqlx module: get_session through URI mySession = mysqlx.get_session(__uripwd) print mySession, '\n' if mySession.uri == __displayuri: print 'Session using right URI\n' else: print 'Session using wrong URI\n' mySession.close() #@ mysqlx module: get_session through URI and password mySession = mysqlx.get_session(__uri, __pwd) print mySession, '\n'
@author: Auriane25 """ import json def charge(fichier): with open(fichier) as f: return json.load(f) import mysqlx session = mysqlx.get_session({ "host": "10.13.237.15", "port": 33060, "user": "******", "password": "******" }) db = session.get_schema("world_x") def lecture(fichier): # Le nom de la collection temporaire nomColl = "temp" # Crée la collection temporaire maColl maColl = db.create_collection(nomColl) # charge le fichier dans la variable json
def test_get_default_schema(self): schema = self.session.get_default_schema() self.assertTrue(schema, mysqlx.Schema) self.assertEqual(schema.get_name(), self.connect_kwargs["schema"]) self.assertTrue(schema.exists_in_database()) # Test None value is returned if no schema name is specified settings = self.connect_kwargs.copy() settings.pop("schema") session = mysqlx.get_session(settings) schema = session.get_default_schema() self.assertIsNone(schema, "None value was expected but got '{}'".format(schema)) session.close() # Test SQL statements not fully qualified, which must not raise error: # mysqlx.errors.OperationalError: No database selected self.session.sql('CREATE DATABASE my_test_schema').execute() self.session.sql('CREATE TABLE my_test_schema.pets(name VARCHAR(20))' ).execute() settings = self.connect_kwargs.copy() settings["schema"] = "my_test_schema" session = mysqlx.get_session(settings) schema = session.get_default_schema() self.assertTrue(schema, mysqlx.Schema) self.assertEqual(schema.get_name(), "my_test_schema") result = session.sql('SHOW TABLES').execute().fetch_all() self.assertEqual("pets", result[0][0]) self.session.sql('DROP DATABASE my_test_schema').execute() self.assertFalse(schema.exists_in_database()) self.assertRaises(mysqlx.ProgrammingError, session.get_default_schema) session.close() # Test without default schema configured at connect time (passing None) settings = self.connect_kwargs.copy() settings["schema"] = None build_uri(**settings) session = mysqlx.get_session(settings) schema = session.get_default_schema() self.assertIsNone(schema, "None value was expected but got '{}'".format(schema)) session.close() # Test not existing default schema at get_session raise error settings = self.connect_kwargs.copy() settings["schema"] = "nonexistent" self.assertRaises(InterfaceError, mysqlx.get_session, settings) # Test BUG#28942938: 'ACCESS DENIED' error for unauthorized user tries # to use the default schema if not exists at get_session self.session.sql("DROP USER IF EXISTS 'def_schema'@'%'").execute() self.session.sql("CREATE USER 'def_schema'@'%' IDENTIFIED WITH " "mysql_native_password BY 'test'").execute() settings = self.connect_kwargs.copy() settings['user'] = '******' settings['password'] = '******' settings["schema"] = "nonexistent" # a) Test with no Granted privileges with self.assertRaises(InterfaceError) as context: _ = mysqlx.get_session(settings) # Access denied for this user self.assertEqual(1044, context.exception.errno) # Grant privilege to one unrelated schema self.session.sql("GRANT ALL PRIVILEGES ON nonexistent.* TO " "'def_schema'@'%'").execute() with self.assertRaises(InterfaceError) as context: _ = mysqlx.get_session(settings) # Schema does not exist self.assertNotEqual(1044, context.exception.errno)
def setUp(self): self.connect_kwargs = tests.get_mysqlx_config() self.session = mysqlx.get_session(self.connect_kwargs) self.session.sql('DROP DATABASE IF EXISTS my_test_schema').execute()
def test_close(self): session = mysqlx.get_session(self.connect_kwargs) schema = session.get_schema(self.schema_name) session.close() self.assertRaises(mysqlx.OperationalError, schema.exists_in_database)
import mysqlx import os # Connect to server on localhost session = mysqlx.get_session({ 'host': '127.0.0.1', 'port': 18001, 'user': '******', 'password': '******' }) #schema = session.get_schema('world_x') schema = session.get_schema('persondoc') #collection = schema.get_collection('CountryInfo') collection = schema.get_collection('person') #result = collection.find("Name='Spain'").limit(1).execute() result = collection.find("name='Martin'").limit(1).execute() # Print document docs = result.fetch_all() print(docs[0]) session.close()
# # Introducing the MySQL 8 Document Store # # This file contains a demonstration of how to read a document from a # collection. It shows how to iterate over embedded # data in the form of lists rather than a dependent collection. # # Database name = contact_list3 # # Dr. Charles Bell, 2017 # import mysqlx # Connect to server session = mysqlx.get_session("root:password@localhost:33060") # Get the schema schema = session.get_schema("contact_list3") # Get the collection contacts = schema.get_collection("contacts") # Read the row row = contacts.find("first = '{0}' and last = '{1}'".format('Bill', 'Smith')).execute() contact = row.fetch_one() addresses = contact["addresses"] phones = contact["phones"] email_addresses = contact["email_addresses"]
import mysqlx import sales user = '******' password = '******' network_service = 'localhost' creds = { 'host': 'localhost', 'port': 33060, 'user': user, 'password': password } sess = mysqlx.get_session(creds) db = sess.get_schema('sig_ron') corp = db.get_collection('corporations').find('True').execute().fetch_one() regions_coll = db.get_collection('regions') for region in corp['regions']: regions_coll.find('_id = region') ################################################################################ # This gets hierarchy sales for a given date range ################################################################################ start_date = '2018-01-01' end_date = '2018-12-01'
def test_connection_attributes(self): # Validate an error is raised if URL user defined connection attributes # through a connection URL name is duplicate connection_attributes = { "foo": "bar", "repeated": "attribute", "baz": "zoom", } uri = build_uri(user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], host=self.connect_kwargs["host"], port=self.connect_kwargs["port"], schema=self.connect_kwargs["schema"], connection_attributes=connection_attributes) uri = "{},repeated=duplicate_attribute]".format(uri[0:-1]) with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(uri, "{}") _ = my_client.get_session() self.assertTrue('Duplicate key ' in context.exception.msg, "error found: {}".format(context.exception.msg)) # Test error is raised for attribute name starting with '_' connection_attributes = [ {"foo": "bar", "_baz": "zoom"}, {"_baz": "zoom"}, {"foo": "bar", "_baz": "zoom", "puuuuum": "kaplot"} ] for conn_attr in connection_attributes: connect_kwargs = self.connect_kwargs.copy() connect_kwargs["connection_attributes"] = conn_attr with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(connect_kwargs, "{}") _ = my_client.get_session() self.assertTrue("connection-attributes" in context.exception.msg) self.assertTrue("cannot start with '_'" in context.exception.msg) # Test error is raised for attribute name size exceeds 32 characters connection_attributes = [ {"foo": "bar", "p{}w".format("o"*31): "kaplot"}, {"p{}w".format("o"*31): "kaplot"}, {"baz": "zoom", "p{}w".format("o"*31): "kaplot", "a": "b"} ] for conn_attr in connection_attributes: connect_kwargs = self.connect_kwargs.copy() connect_kwargs["connection_attributes"] = conn_attr with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(connect_kwargs, "{}") _ = my_client.get_session() self.assertTrue("exceeds 32 characters limit size" in context.exception.msg) # Test error is raised for attribute value size exceeds 1024 characters connection_attributes = [ {"foo": "bar", "pum": "kr{}nk".format("u"*1024)}, {"pum": "kr{}nk".format("u"*1024)}, {"baz": "zoom", "pum": "kr{}nk".format("u"*1024), "a": "b"} ] for conn_attr in connection_attributes: connect_kwargs = self.connect_kwargs.copy() connect_kwargs["connection-attributes"] = conn_attr with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(connect_kwargs, "{}") _ = my_client.get_session() self.assertTrue("exceeds 1024 characters limit size" in context.exception.msg) # Validate the user defined attributes are created in the server # Test user defined connection attributes through a connection URL connection_attributes = { "foo": "bar", "baz": "zoom", "quash": "", "puuuuum": "kaplot" } uri = build_uri(user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], host=self.connect_kwargs["host"], port=self.connect_kwargs["port"], schema=self.connect_kwargs["schema"], connection_attributes=connection_attributes) # Verify user defined session-connection-attributes are in the server my_session = mysqlx.get_session(uri) row = my_session.sql("SHOW VARIABLES LIKE \"pseudo_thread_id\"").\ execute().fetch_all()[0] get_attrs = ("SELECT ATTR_NAME, ATTR_VALUE FROM " "performance_schema.session_account_connect_attrs " "where PROCESSLIST_ID = \"{}\"") rows = my_session.sql(get_attrs.format(row.get_string('Value'))).\ execute().fetch_all() expected_attrs = connection_attributes.copy() expected_attrs.update({ "_pid": str(os.getpid()), "_platform": self.platform_arch, "_source_host": socket.gethostname(), "_client_name": "mysql-connector-python", "_client_license": self.client_license, "_client_version": ".".join([str(x) for x in VERSION[0:3]]), "_os": self.os_ver }) # Note that for an empty string "" value the server stores a Null value expected_attrs["quash"] = "None" for row in rows: self.assertEqual(expected_attrs[row.get_string('ATTR_NAME')], row.get_string('ATTR_VALUE'), "Attribute {} with value {} differs of {}".format( row.get_string('ATTR_NAME'), row.get_string('ATTR_VALUE'), expected_attrs[row.get_string('ATTR_NAME')]))
def test_connection_attributes(self): # Validate an error is raised if URL user defined connection attributes # through a connection URL name is duplicate connection_attributes = { "foo": "bar", "repeated": "attribute", "baz": "zoom", } uri = build_uri(user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], host=self.connect_kwargs["host"], port=self.connect_kwargs["port"], schema=self.connect_kwargs["schema"], connection_attributes=connection_attributes) uri = "{},repeated=duplicate_attribute]".format(uri[0:-1]) with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(uri, "{}") _ = my_client.get_session() self.assertTrue('Duplicate key ' in context.exception.msg, "error found: {}".format(context.exception.msg)) # Test error is raised for attribute name starting with '_' connection_attributes = [{ "foo": "bar", "_baz": "zoom" }, { "_baz": "zoom" }, { "foo": "bar", "_baz": "zoom", "puuuuum": "kaplot" }] for conn_attr in connection_attributes: connect_kwargs = self.connect_kwargs.copy() connect_kwargs["connection_attributes"] = conn_attr with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(connect_kwargs, "{}") _ = my_client.get_session() self.assertTrue("connection-attributes" in context.exception.msg) self.assertTrue("cannot start with '_'" in context.exception.msg) # Test error is raised for attribute name size exceeds 32 characters connection_attributes = [{ "foo": "bar", "p{}w".format("o" * 31): "kaplot" }, { "p{}w".format("o" * 31): "kaplot" }, { "baz": "zoom", "p{}w".format("o" * 31): "kaplot", "a": "b" }] for conn_attr in connection_attributes: connect_kwargs = self.connect_kwargs.copy() connect_kwargs["connection_attributes"] = conn_attr with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(connect_kwargs, "{}") _ = my_client.get_session() self.assertTrue( "exceeds 32 characters limit size" in context.exception.msg) # Test error is raised for attribute value size exceeds 1024 characters connection_attributes = [{ "foo": "bar", "pum": "kr{}nk".format("u" * 1024) }, { "pum": "kr{}nk".format("u" * 1024) }, { "baz": "zoom", "pum": "kr{}nk".format("u" * 1024), "a": "b" }] for conn_attr in connection_attributes: connect_kwargs = self.connect_kwargs.copy() connect_kwargs["connection-attributes"] = conn_attr with self.assertRaises(InterfaceError) as context: my_client = mysqlx.get_client(connect_kwargs, "{}") _ = my_client.get_session() self.assertTrue( "exceeds 1024 characters limit size" in context.exception.msg) # Validate the user defined attributes are created in the server # Test user defined connection attributes through a connection URL connection_attributes = { "foo": "bar", "baz": "zoom", "quash": "", "puuuuum": "kaplot" } uri = build_uri(user=self.connect_kwargs["user"], password=self.connect_kwargs["password"], host=self.connect_kwargs["host"], port=self.connect_kwargs["port"], schema=self.connect_kwargs["schema"], connection_attributes=connection_attributes) # Verify user defined session-connection-attributes are in the server my_session = mysqlx.get_session(uri) row = my_session.sql("SHOW VARIABLES LIKE \"pseudo_thread_id\"").\ execute().fetch_all()[0] get_attrs = ("SELECT ATTR_NAME, ATTR_VALUE FROM " "performance_schema.session_account_connect_attrs " "where PROCESSLIST_ID = \"{}\"") rows = my_session.sql(get_attrs.format(row.get_string('Value'))).\ execute().fetch_all() expected_attrs = connection_attributes.copy() expected_attrs.update({ "_pid": str(os.getpid()), "_platform": self.platform_arch, "_source_host": socket.gethostname(), "_client_name": "mysql-connector-python", "_client_license": self.client_license, "_client_version": ".".join([str(x) for x in VERSION[0:3]]), "_os": self.os_ver }) # Note that for an empty string "" value the server stores a Null value expected_attrs["quash"] = "None" for row in rows: self.assertEqual( expected_attrs[row.get_string('ATTR_NAME')], row.get_string('ATTR_VALUE'), "Attribute {} with value {} differs of {}".format( row.get_string('ATTR_NAME'), row.get_string('ATTR_VALUE'), expected_attrs[row.get_string('ATTR_NAME')]))
import mysqlx # Connect to server on localhost session = mysqlx.get_session({ "host": "localhost", "port": 33060, "user": "******", "password": "******" }) # Get schema object schema = session.get_schema("test") # Use the collection "my_collection" collection = schema.get_collection("my_collection") collection.add("{'name:','MySQL'}").execute() # Specify which document to find with Collection.find() result = collection.find("name = 'MySQL'").execute() # Print document docs = result.fetch_all() print docs #print("Name: {0}".format(docs[0]["name"])) session.close()
import mysqlx from config import connect_args # Create the session db = mysqlx.get_session(schema="py_test_db", **connect_args) # Retrieve the default schema # (py_test_db) py_schema = db.get_default_schema() print("Schema name: {0} - Exists? {1}".format(py_schema.name, py_schema.exists_in_database())) # If py_test_db does not exist, # create it if (not py_schema.exists_in_database()): db.create_schema(py_schema.name) print("Schema name: {0} - Exists? {1}".format(py_schema.name, py_schema.exists_in_database())) # Get the world schema w_schema = db.get_schema("world") print("Schema name: {0} - Exists? {1}".format(w_schema.name, w_schema.exists_in_database())) # Get the session object of the world # schema and see if it is the same as # the db object. w_session = w_schema.get_session() print("db == w_session? {0}".format(db == w_session))
# Import the MySQL X module import mysqlx # Get a session with a URI mysqlx_session = mysqlx.get_session("root:secret@localhost:33060") # Check the connection if not mysqlx_session.is_open(): print("Connection failed!") exit(1) # Create a schema. schema = mysqlx_session.create_schema("animals") # Create a new collection pets = schema.create_collection("pets_json", True) # Insert some documents pets.add({ 'name': 'Violet', 'age': 6, 'breed': 'dachshund', 'type': 'dog' }).execute() pets.add({ 'name': 'JonJon', 'age': 15, 'breed': 'poodle', 'type': 'dog' }).execute() pets.add({ 'name': 'Mister', 'age': 4, 'breed': 'siberian khatru', 'type': 'cat' }).execute()
import mysqlx from config import connect_args db = mysqlx.get_session(**connect_args) schema = db.get_schema("py_test_db") city_col = schema.get_collection("city") db.start_transaction() # Get the current population statement = city_col.find( "Geography.State = :state") statement.fields("Name AS CityName", "Demographics.Population AS Population") statement.sort("Name") statement.bind("state", "Victoria") before = statement.execute() # Update the population for cities # in the state of Victoria to increase # the population with 10% result = city_col.modify( "Geography.State = :state") \ .set("Demographics.Population", "FLOOR(Demographics.Population * 1.10)") \ .bind("state", "Victoria") \ .execute() print("Number of affected docs: {0}" .format(result.get_affected_items_count()))
def test___init__(self): bad_config = { "host": "bad_host", "port": "", "username": "******", "password": "" } self.assertRaises(InterfaceError, mysqlx.Session, bad_config) host = self.connect_kwargs["host"] port = self.connect_kwargs["port"] user = self.connect_kwargs["user"] password = self.connect_kwargs["password"] # Session to a farm using one of many routers (prios) # Loop during connect because of network error (succeed) routers = [{"host": "bad_host","priority": 100}, {"host": host, "port": port, "priority": 98}] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # Session to a farm using one of many routers (incomplete prios) routers = [{"host": "bad_host", "priority": 100}, {"host": host, "port": port}] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except ProgrammingError as err: self.assertEqual(4000, err.errno) # Session to a farm using invalid priorities (out of range) routers = [{"host": "bad_host", "priority": 100}, {"host": host, "port": port, "priority": 101}] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except ProgrammingError as err: self.assertEqual(4007, err.errno) # Establish an Session to a farm using one of many routers (no prios) routers = [{"host": "bad_host"}, {"host": host, "port": port}] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # Break loop during connect (non-network error) uri = build_uri(user=user, password="******", routers=routers) self.assertRaises(InterfaceError, mysqlx.get_session, uri) # Break loop during connect (none left) uri = "mysqlx://{0}:{1}@[bad_host, another_bad_host]".format(user, password) self.assertRaises(InterfaceError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except InterfaceError as err: self.assertEqual(4001, err.errno) # Invalid option with URI uri = "mysqlx://{0}:{1}@{2}:{3}?invalid=option" \ "".format(user, password, host, port) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) # Invalid option with dict config = { "user": user, "password": password, "host": host, "port": port, "invalid": "option" } self.assertRaises(ProgrammingError, mysqlx.get_session, config) # Invalid option with kwargs self.assertRaises(ProgrammingError, mysqlx.get_session, **config) # SocketSteam.is_socket() session = mysqlx.get_session(user=user, password=password, host=host, port=port) self.assertFalse(session._connection.stream.is_socket())
def _load_data_from_db(self): self.session = mysqlx.get_session(mysql_config) self.a = datetime.today().strftime("%b %d %Y %H:%M") if self.admin_mode or self.moder_mode: try: self.result = self.session.sql( 'Select mark,number,Date(date),id from garage.avto order by id;' ).execute().fetch_all() except Exception as e: return for i, row_data in enumerate(self.result): self.tableWidget.insertRow(i) for j, data in enumerate(row_data): if j == 2: try: data = data.year except Exception as e: continue self.tableWidget.setItem( i, j, QtWidgets.QTableWidgetItem(str(data))) if self.admin_mode or self.moder_mode: self.result = self.session.sql( 'Select * from garage.driver;').execute().fetch_all() for i, row_data in enumerate(self.result): self.tableWidget_2.insertRow(i) for j, data in enumerate(row_data): self.tableWidget_2.setItem( i, j, QtWidgets.QTableWidgetItem(str(data))) query = """ Select r.start, r.finish, r.plane, r.success, r.client, r.id_avto, d.name, r.id_driver, r.id from garage.route r inner join garage.driver d on r.id_driver=d.id inner join garage.avto a on r.id_avto=a.id """ if not (self.admin_mode or self.moder_mode): query += ' where r.id_driver = {}'.format(self.id_driver) query += " order by r.id" try: self.result = self.session.sql(query).execute().fetch_all() except Exception as e: pass for i, row_data in enumerate(self.result): self.tableWidget_3.insertRow(i) for j, data in enumerate(row_data): self.tableWidget_3.setItem( i, j, QtWidgets.QTableWidgetItem(str(data))) if self.admin_mode: try: self.result = self.session.sql(""" Select u.id, p.name,u.login,u.password,u.name from garage.user u inner join garage.position p on p.id=u.id_position order by u.id; """).execute().fetch_all() except Exception as e: print(e) for i, row_data in enumerate(self.result): self.tableWidget_4.insertRow(i) for j, data in enumerate(row_data): self.tableWidget_4.setItem( i, j, QtWidgets.QTableWidgetItem(str(data))) self.session.close()
def test_connect_timeout(self): config = self.connect_kwargs.copy() # 0 ms disables timouts on socket connections config["connect-timeout"] = 0 session = mysqlx.get_session(config) session.close() # 10000 ms should be time enough to connect config["connect-timeout"] = 10000 session = mysqlx.get_session(config) session.close() # Use connect timeout in URI session = mysqlx.get_session(build_uri(**config)) session.close() # Timeout for an unreachable host # https://en.wikipedia.org/wiki/IPv4#Special-use_addresses config["host"] = "198.51.100.255" config["connect-timeout"] = 2000 self.assertRaises(mysqlx.TimeoutError, mysqlx.get_session, config) # Multi-host scenarios # Connect to a secondary host if the primary fails routers = [ {"host": "198.51.100.255", "port": config["port"], "priority": 100}, {"host": "127.0.0.1", "port": config["port"], "priority": 90} ] uri = build_uri(user=config["user"], password=config["password"], connect_timeout=2000, routers=routers) session = mysqlx.get_session(uri) session.close() # Fail to connect to all hosts routers = [ {"host": "198.51.100.255", "port": config["port"], "priority": 100}, {"host": "192.0.2.255", "port": config["port"], "priority": 90} ] uri = build_uri(user=config["user"], password=config["password"], connect_timeout=2000, routers=routers) try: mysqlx.get_session(uri) self.fail("It should not connect to any unreachable host") except mysqlx.TimeoutError as err: self.assertEqual(err.msg, "All server connection attempts were aborted. " "Timeout of 2000 ms was exceeded for each " "selected server") # Trying to establish a connection with a wrong password should not # wait for timeout config["host"] = "127.0.0.1" config["password"] = "******" config["connect-timeout"] = 2000 time_start = time.time() self.assertRaises(InterfaceError, mysqlx.get_session, config) time_elapsed = time.time() - time_start session.close() if time_elapsed >= config["connect-timeout"]: self.fail("Trying to establish a connection with a wrong password " "should not wait for timeout") # The connection timeout value must be a positive integer config["connect-timeout"] = -1 self.assertRaises(TypeError, mysqlx.get_session, config) config["connect-timeout"] = 10.0983 self.assertRaises(TypeError, mysqlx.get_session, config) config["connect-timeout"] = "abc" self.assertRaises(TypeError, mysqlx.get_session, config)
def test___init__(self): bad_config = { "host": "bad_host", "port": "", "username": "******", "password": "" } self.assertRaises(TypeError, mysqlx.Session, bad_config) host = self.connect_kwargs["host"] port = self.connect_kwargs["port"] user = self.connect_kwargs["user"] password = self.connect_kwargs["password"] # Session to a farm using one of many routers (prios) # Loop during connect because of network error (succeed) routers = [{ "host": "bad_host", "priority": 100 }, { "host": host, "port": port, "priority": 98 }] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # Session to a farm using one of many routers (incomplete prios) routers = [{ "host": "bad_host", "priority": 100 }, { "host": host, "port": port }] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except ProgrammingError as err: self.assertEqual(4000, err.errno) # Session to a farm using invalid priorities (out of range) routers = [{ "host": "bad_host", "priority": 100 }, { "host": host, "port": port, "priority": 101 }] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except ProgrammingError as err: self.assertEqual(4007, err.errno) # Establish an Session to a farm using one of many routers (no prios) routers = [{"host": "bad_host"}, {"host": host, "port": port}] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # Break loop during connect (non-network error) uri = build_uri(user=user, password="******", routers=routers) self.assertRaises(InterfaceError, mysqlx.get_session, uri) # Break loop during connect (none left) uri = "mysqlx://{0}:{1}@[bad_host, another_bad_host]".format( user, password) self.assertRaises(InterfaceError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except InterfaceError as err: self.assertEqual(4001, err.errno) # Invalid option with URI uri = "mysqlx://{0}:{1}@{2}:{3}?invalid=option" \ "".format(user, password, host, port) self.assertRaises(ProgrammingError, mysqlx.get_session, uri) # Invalid option with dict config = { "user": user, "password": password, "host": host, "port": port, "invalid": "option" } self.assertRaises(ProgrammingError, mysqlx.get_session, config) # Invalid option with kwargs self.assertRaises(ProgrammingError, mysqlx.get_session, **config)
# Connecting to MySQL and working with a Session import mysqlx # Connect to a dedicated MySQL server using a connection URL mySession = mysqlx.get_session('mike:s3cr3t!@localhost') # Get a list of all available schemas schemaList = mySession.get_schemas() print 'Available schemas in this session:\n' # Loop over all available schemas and print their name for schema in schemaList: print '%s\n' % schema.name mySession.close()
def test_ssl_connection(self): config = {} config.update(self.connect_kwargs) socket = config.pop("socket") # Secure by default session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() # Error on setting Client key without Client Certificate config["ssl-key"] = tests.SSL_KEY self.assertRaises(InterfaceError, mysqlx.get_session, config) # Error on settings CRL without setting CA Certificate config["ssl-crl"] = "/dummy/path" self.assertRaises(InterfaceError, mysqlx.get_session, config) config.pop("ssl-crl") # Error on setting SSL Mode to disabled with any SSL option config["ssl-mode"] = "disabled" self.assertRaises(InterfaceError, mysqlx.get_session, config) # Error on setting SSL Mode to verify_* without ssl_ca config["ssl-mode"] = "verify_ca" self.assertRaises(InterfaceError, mysqlx.get_session, config) config["ssl-mode"] = "verify_identity" self.assertRaises(InterfaceError, mysqlx.get_session, config) # Error on SSL Mode set to required with CA set config["ssl-ca"] = tests.SSL_CA config["ssl-cert"] = tests.SSL_CERT config["ssl-mode"] = "required" self.assertRaises(InterfaceError, mysqlx.get_session, config) # Connection with ssl parameters config["ssl-mode"] = "verify_identity" session = mysqlx.get_session(config) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_active'").execute().fetch_all() self.assertEqual("ON", res[0][1]) res = mysqlx.statement.SqlStatement( session._connection, "SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all() self.assertTrue("TLS" in res[0][1]) session.close() # Error if ssl-mode=disabled and ssl_* set extra = [("ssl_mode", "disabled"), ("ssl_ca", "({0})".format(tests.SSL_CA))] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) self.assertRaises(InterfaceError, mysqlx.get_session, uri) # Error if invalid ssl-mode extra = [("ssl_mode", "invalid")] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) self.assertRaises(InterfaceError, mysqlx.get_session, uri) # Parsing SSL Certificates extra = [("ssl_mode", "verify_ca"), ("ssl_ca", file_uri(tests.SSL_CA, False)), ("ssl_key", file_uri(tests.SSL_KEY, False)), ("ssl_cert", file_uri(tests.SSL_CERT, False))] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) session = mysqlx.get_session(uri) extra = [("ssl_mode", "verify_ca"), ("ssl_ca", file_uri(tests.SSL_CA)), ("ssl_key", file_uri(tests.SSL_KEY)), ("ssl_cert", file_uri(tests.SSL_CERT))] uri = build_uri(**dict(list(self.connect_kwargs.items()) + extra)) session = mysqlx.get_session(uri)
import mysqlx from collections import defaultdict import pickle user_rating = defaultdict(dict) session = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******' }) schema = session.get_schema('moviews') ratings = schema.get_table('user_ratings') rows = ratings.select('userId', 'rating', 'movieId').execute().fetch_all() for row in rows: uid = row['userId'] movie_id = row['movieId'] rating = row['rating'] user_rating[uid][movie_id] = rating pickle.dump(user_rating, open("user_rating_matrix.p", "wb")) print("Done!")
import mysqlx # Connect to server on localhost session = mysqlx.get_session({ 'host': '127.0.0.1', 'port': 33060, 'user': '******', 'password': '******' }) schema = session.get_default_schema() cursor = session
def test___init__(self): bad_config = { "host": "bad_host", "port": "", "username": "******", "password": "" } self.assertRaises(TypeError, mysqlx.XSession, bad_config) host = self.connect_kwargs["host"] port = self.connect_kwargs["port"] user = self.connect_kwargs["user"] password = self.connect_kwargs["password"] # XSession to a farm using one of many routers (prios) # Loop during connect because of network error (succeed) routers = [{"host": "bad_host","priority": 100}, {"host": host, "port": port, "priority": 98}] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # XSession to a farm using one of many routers (incomplete prios) routers = [{"host": "bad_host", "priority": 100}, {"host": host, "port": port}] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(mysqlx.errors.ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except mysqlx.errors.ProgrammingError as err: self.assertEqual(4000, err.errno) # XSession to a farm using invalid priorities (out of range) routers = [{"host": "bad_host", "priority": 100}, {"host": host, "port": port, "priority": 101}] uri = build_uri(user=user, password=password, routers=routers) self.assertRaises(mysqlx.errors.ProgrammingError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except mysqlx.errors.ProgrammingError as err: self.assertEqual(4007, err.errno) # Establish an XSession to a farm using one of many routers (no prios) routers = [{"host": "bad_host"}, {"host": host, "port": port}] uri = build_uri(user=user, password=password, routers=routers) session = mysqlx.get_session(uri) session.close() # Break loop during connect (non-network error) uri = build_uri(user=user, password="******", routers=routers) self.assertRaises(mysqlx.errors.InterfaceError, mysqlx.get_session, uri) # Break loop during connect (none left) uri = "mysqlx://{0}:{1}@[bad_host, another_bad_host]".format(user, password) self.assertRaises(mysqlx.errors.InterfaceError, mysqlx.get_session, uri) try: session = mysqlx.get_session(uri) except mysqlx.errors.InterfaceError as err: self.assertEqual(4001, err.errno)