8.8.3 Providing Information to Choose a MySQL Server

If you create a Fabric connection without providing any information about which data to access, the connection cannot function. To access a database, you must provide the driver with either of these types of information:

The following discussion describes both ways of providing information. You do this by setting one or more properties of the Fabric connection object using its set_property() method, so the discussion begins by setting forth the sharding-related properties.. In the examples, fcnx represents the Fabric connection object, created as shown in Section 8.8.2, “Requesting a Fabric Connection”.

Note

set_property() does not connect. The connection is opened when a cursor is requested from the Fabric connection object or when its cmd_query() or cmd_query_iter() method is invoked.

These set_property() arguments are shard-related:

group and tables are mutually exclusive, so you specify only one of them. Applicability of the remaining arguments depends on which of group or tables you use:

If you specify group:

If you specify tables:

When the mode argument is applicable, these values are permitted:

When the scope argument is applicable, these values are permitted:

Providing the name of a high-availability group specifies that we know exactly the set of database servers that with which to interact. To do this, set the group property using the set_property() method:

fcnx.set_property(group='myGroup')

Providing shard information avoids the need to choose a high-availability group manually and permits Connector/Python to do so based on information from the MySQL Fabric server.

Whether operations use RANGE or HASH is transparent to the user. The information is provided by Fabric and Connector/Python uses the correct mechanism automatically.

To specify shard tables and shard keys, use the tables and key attributes of the set_property() method.

The format of each shard table is usually given as 'db_name.tbl_name'. Because one or more tables can be specified, the tables argument to set_property() is specified as a tuple or list:

fcnx.set_property(tables=['employees.employees'], key=40)
cur = fcnx.cursor()
# do operations for employee with emp_no 40
fcnx.close()

By default, operations occur in local scope, or the scope property can be given to specify local or global scope explicitly. For local operations (as in the preceding example), the key argument must be specified to indicate which row to use. For global operations, do not specify the key attribute because the operation is performed on all rows in the table:

fcnx.set_property(tables=['employees.employees'], scope=fabric.SCOPE_GLOBAL)
cur = fcnx.cursor()
cur.execute("UPDATE employees SET last_name = UPPER(last_name)")
cnx.commit()
fcnx.close()

The default mode is read/write, so the driver connects to the master. The mode property can be given to specify read/write or read-only mode explicitly:

fcnx.set_property(group='myGroup', mode=fabric.MODE_READWRITE)
cur = fcnx.cursor()
cur.execute("UPDATE employees SET last_name = UPPER(last_name)")
cnx.commit()
fcnx.close()

Applications for which read-only mode is sufficient can specify a mode attribute of fabric.MODE_READONLY. In this case, a connection is established to a slave if one is available, or to the master otherwise.