Python mysql context prepared an INSERT statement by disabling text

Question

I am trying to insert a large row into a MySQL database using pythons mysql.connector. The problem I am facing is that long lines are interrupted at some point when using prepared statements. I am currently using the MySQL Connector / Python, which is available on MySQL.com. I used the following code to duplicate the problem that I have.

db = mysql.connector.connect(**creditials)
cursor = db.cursor()

value = []

for x in range(0, 2000):
    value.append(str(x+1))

value = " ".join(value)

cursor.execute("""
                CREATE TABLE IF NOT EXISTS test ( 
                    pid VARCHAR(50),
                    name VARCHAR(120),
                    data LONGTEXT,
                    PRIMARY KEY(pid)
                )  
                """)
db.commit()


#this works as expected
print("Test 1")
cursor.execute("REPLACE INTO test (pid, name, data) VALUES ('try 1', 'Description', '{0}')".format(value))
db.commit()
cursor.close()


#this does not work
print("Test 2")
cursor = db.cursor(prepared=True)
cursor.execute("""REPLACE INTO test (pid, name, data) VALUE (?, ?, ?)""", ('try 2', 'Description2', value))
db.commit()
cursor.close()

Test 1 works as expected and saves all numbers until 2000, but test 2 is disabled immediately after number 65. I would rather use prepared statements than try to misinform the incoming lines myself. Any help was appreciated.

Additional Information:

Computer: Windows 7 64 bit

Python: Tried both python 3.4 and 3.3

MYSQL: 5.6.17 ( WAMP)

: MySQL/Python

Answer 1

MySQL Connector , . , , INT VARCHAR TEXT .. , "" . , Python MySQL, VARCHAR. VARCHAR , , . , .

:

• -

  MySQL Connector BLOB TEXT ( , ). , .

  import StringIO
  
  ...
  
  cursor = db.cursor(prepared=True)
  cursor.execute("""REPLACE INTO test (pid, name, data) VALUES (?, ?, ?)""",
                 ('try 2', 'Description', StringIO.String(value)))
  cursor.close()
  db.commit()
  

• MySQL Connector

  prepared=True , SQL . , MySQL- . SQL- , .

  cursor = db.cursor()
  cursor.execute("""REPLACE INTO test (pid, name, data) VALUES (%s, %s, %s)""",
                 ('try 2', 'Description', value))
  cursor.close()
  db.commit()
  

• MySQL

  Python MySQL:

  • MySQLdb
  • oursql