Parameter Binding

It is good practice to not use values directly on expression strings. The way this is using a parameter or place holder on the expression to replace the real value, later the value is associated to the defined parameter or place holder.

There are two ways to achieve this:

Anonymous Binding

Available on SQL strings, allows removing values from expression through the use of place holders. To finally associate the corresponding value just call the .bind(<value>) function.

session = mysqlx.get_session('mike:s3cr3t!@localhost:33060')
 
# Loads the adult customers whose name starts with S
result = session.sql('select * from test.customers where name like ? and age < ?').bind('S%').bind(18).execute()
 
The bind function must be called for each place holder on the expression, otherwise an error will occur.

Named Binding

Available on the CRUD operations, allows removing values from expressions through the use of named parameters. To finally associate the corresponding parameter values, call the .bind(<parameter>, <value>) function.

# Collection.find() function with hardcoded values
myColl = db.get_collection('my_collection')
 
myRes1 = myColl.find('age = 15').execute()
 
# Using the .bind() function to bind parameters
myRes2 = myColl.find('name = :param1 AND age = :param2').bind('param1','jack').bind('param2', 17).execute()
 
# Using named parameters
myColl.modify('name = :param').set('age', 37).bind('param', 'clare').execute()
 
# Binding works for all CRUD statements except add()
myRes3 = myColl.find('name like :param').bind('param', 'J%').execute()

Each CRUD operation creates a binding context, in order to succeed all the defined parameters must have a value. A parameter could be used several times on the operation, even so, only one value can be assigned to it. If bind is called several times for the same parameter the last value will override the previous ones.

# one bind() per parameter
myColl = db.get_collection('relatives')
juniors = myColl.find('alias = "jr"').execute().fetch_all()
 
for junior in juniors:
  myColl.modify('name = :param'). \
    set('parent_name',mysqlx.expr(':param')). \
    bind('param', junior.name).execute()