title: Use of '.format()' vs. '%s' in cursor.execute() for mysql JSON field, with Python mysql.connector,
body: <p>My objective is to store a JSON object into a MySQL database field of type json, using the <code>mysql.connector</code> library.</p>
  
  <pre><code>import mysql.connector
  import json
  
  jsonData = json.dumps(origin_of_jsonData)
  
  cnx = mysql.connector.connect(**config_defined_elsewhere)
  cursor = cnx.cursor()
  cursor.execute('CREATE DATABASE dataBase')
  cnx.database = 'dataBase'
  cursor = cnx.cursor()
  cursor.execute('CREATE TABLE table (id_field INT NOT NULL, json_data_field JSON NOT NULL, PRIMARY KEY (id_field))')
  </code></pre>
  
  <p>Now, the code below WORKS just fine, the focus of my question is the use of '%s':</p>
  
  <pre><code>insert_statement = "INSERT INTO table (id_field, json_data_field) VALUES (%s, %s)"
  values_to_insert = (1, jsonData)
  cursor.execute(insert_statement, values_to_insert)
  </code></pre>
  
  <p>My problem with that: I am very strictly adhering to the use of <code>'...{}'.format(aValue)</code> (or <code>f'...{aValue}'</code>) when combining variable aValue(s) into a string, thus avoiding the use of <code>%s</code> (whatever my reasons for that, let's not debate them here - but it is how I would like to keep it wherever possible, hence my question).</p>
  
  <p>In any case, I am simply unable, whichever way I try, to create something that stores the jsonData into the mySql dataBase using something that resembles the above structure and uses <code>'...{}'.format()</code> (in whatever shape or form) instead of <code>%s</code>. For example, I have (among many iterations) tried</p>
  
  <pre><code>insert_statement = "INSERT INTO table (id_field, json_data_field) VALUES ({}, {})".format(1, jsonData)
  cursor.execute(insert_statement)
  </code></pre>
  
  <p>but no matter how I turn and twist it, I keep getting the following error:</p>
  
  <blockquote>
    <p>ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[some_content_from_jsonData})]' at line 1</p>
  </blockquote>
  
  <p><strong>Now my question(s):</strong></p>
  
  <p>1) Is there a way to avoid the use of %s here that I am missing?</p>
  
  <p>2) If not, why? What is it that makes this impossible? Is it the <code>cursor.execute()</code> function, or is it the fact that it is a JSON object, or is it something completely different? Shouldn't <code>{}.format()</code> be able to do everything that <code>%s</code> could do, and more?</p>