How to use python to execute mysql and use replace into - with more than 255 variables ?

Question

Here below is my code that I am using

con.execute("""
REPLACE INTO T(var1,var2,...,var300)VALUES(?,?,....?)""",(var1,var2,...,var300)

This statement works just fine if I have var1-var255 , once I have more than that it gave me an error... So far, I was able to split T into 2 different times

con.execute("""
REPLACE INTO T(var1,var2,...,var150)VALUES(?,?,....?)""",(var1,var2,...,var150)

con.execute("""
REPLACE INTO T(var151,var152,...,var300)VALUES(?,?,....?)""",(var151,var152,...,var300)

This gave me no error , but my final value in table "T" would only values in the second execute statement , all of var1, var2, ... var 150 got replace with null

Answer 1

Have you tried using update instead?

MySQL documentation tells the following: "REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted"

Answer 2

There does not seem to be any inherent problem using more than 255 columns in MySQL, interfaced with MySQLdb:

import MySQLdb
import config

connection = MySQLdb.connect(
    host = config.HOST, user = config.USER,
    passwd = config.PASS, db = 'test')
cursor = connection.cursor()

cols = ['col{i:d}'.format(i =i) for i in range(300)]
types = ['int(11)']*len(cols)
columns = ','.join('{f} {t}'.format(f = f, t = t) for f, t in zip(cols, types))

sql = '''CREATE TABLE IF NOT EXISTS test (
       id INT(11) NOT NULL AUTO_INCREMENT,
       {c},
       PRIMARY KEY (id) 
       )'''.format(c = columns)

cursor.execute(sql)

sql = '''REPLACE INTO test({c}) VALUES ({v})'''.format(
    c = ','.join(cols),
    v = ','.join(['%s']*len(cols)))

cursor.execute(sql, range(300))
result = cursor.fetchall()

This adds rows to test.test without a problem.