Telergoel Telergoel -4 years ago 70
MySQL Question

MySQLdb : all values set to NULL when filling a new column procedurally

I'm working on a simple MySQL database with Python and MySQLdb, and I made some functions in order to manipulate the database without having to type SQL queries in the main script.

One of these functions is supposed to add a new column to my table and immediatly fill it with values from a given python list. Although it does not : the new column is actually created, but all the values inside remain NULL... Have you any idea where I made a mistake ? Here are the main script and the subfunctions it uses.

password = "*********"
name = "database9000"
conn = connect_database(name,password)
add_column("MYTABLE", "MYCOLUMN", mylist, conn) *# where "mylist" is a 9000+ elements list*
disconnect_database(conn)

def connect_database(dbname, password) :
conn = mdb.connect(host = "localhost", user = "root", passwd = password, db = dbname)
return conn

def disconnect_database(conn) :
conn.close()

add_column(tablename, columnname, mylist, conn) :
cs = conn.cursor()
cs.execute("ALTER TABLE %s ADD %s DOUBLE" % (tablename, columnname))
for n in range(len(mylist)) :
cs.execute("UPDATE %s SET %s = %f WHERE id = %d" % (tablename, columnname, float(mylist[n]), n+1))


Thanks

Answer Source

You didn't commit your changes.

Note that in MySQL, schema alteration does not happen within a transaction, but data updates do. So you need to call conn.commit().

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download