0

If coding in python: lshank_quat_x = None will generate "None" directly in SQL insert statement, which causes an error. Is there another way to fix this problem except of making value = "NULL"? (value should not be a string).

Here's the code:

lines = []
# lshank
for i in range(len(sig.IMU.time)):
    # lshank
    lshank_accel_x = sig.IMU.Lshank.a[i][0]
    lshank_accel_y = sig.IMU.Lshank.a[i][1]
    lshank_accel_z = sig.IMU.Lshank.a[i][2]
    lshank_gyro_x = sig.IMU.Lshank.w[i][0]
    lshank_gyro_y = sig.IMU.Lshank.w[i][1]
    lshank_gyro_z = sig.IMU.Lshank.w[i][2]
    lshank_mag_x = sig.IMU.Lshank.m[i][0]
    lshank_mag_y = sig.IMU.Lshank.m[i][1]
    lshank_mag_z = sig.IMU.Lshank.m[i][2]
    lshank_quat_x = "NULL"
    lshank_quat_y = "NULL"
    lshank_quat_z = "NULL"

    # rshank
    rshank_accel_x = sig.IMU.Rshank.a[i][0]
    rshank_accel_y = sig.IMU.Rshank.a[i][1]
    rshank_accel_z = sig.IMU.Rshank.a[i][2]
    rshank_gyro_x = sig.IMU.Rshank.w[i][0]
    rshank_gyro_y = sig.IMU.Rshank.w[i][1]
    rshank_gyro_z = sig.IMU.Rshank.w[i][2]
    rshank_mag_x = sig.IMU.Rshank.m[i][0]
    rshank_mag_y = sig.IMU.Rshank.m[i][1]
    rshank_mag_z = sig.IMU.Rshank.m[i][2]
    rshank_quat_x = "NULL"
    rshank_quat_y = "NULL"
    rshank_quat_z = "NULL"

    # sacrum
    sacrum_accel_x = sig.IMU.Sacrum.a[i][0]
    sacrum_accel_y = sig.IMU.Sacrum.a[i][1]
    sacrum_accel_z = sig.IMU.Sacrum.a[i][2]
    sacrum_gyro_x = sig.IMU.Sacrum.w[i][0]
    sacrum_gyro_y = sig.IMU.Sacrum.w[i][1]
    sacrum_gyro_z = sig.IMU.Sacrum.w[i][2]
    sacrum_mag_x = sig.IMU.Sacrum.m[i][0]
    sacrum_mag_y = sig.IMU.Sacrum.m[i][1]
    sacrum_mag_z = sig.IMU.Sacrum.m[i][2]
    sacrum_quat_x = "NULL"
    sacrum_quat_y = "NULL"
    sacrum_quat_z = "NULL"

    # ground force
    grf_x = sig.force[i][0]
    grf_y = sig.force[i][1]
    grf_z = sig.force[i][2]

    insert_query = f"INSERT INTO {tableName} ({', '.join(field for field in var_list)}) VALUES ({sig.IMU.Lshank.time[i]}, {lshank_accel_x}, {lshank_accel_y}, {lshank_accel_z}, {lshank_gyro_x}, {lshank_gyro_y}, {lshank_gyro_z}, {lshank_mag_x}, {lshank_mag_y}, {lshank_mag_z},{lshank_quat_x},{lshank_quat_y},{lshank_quat_z},{sig.IMU.Rshank.time[i]},{rshank_accel_x}, {rshank_accel_y}, {rshank_accel_z},{rshank_gyro_x},{rshank_gyro_y},{rshank_gyro_z},{rshank_mag_x},{rshank_mag_y},{rshank_mag_z},{rshank_quat_x},{rshank_quat_y},{rshank_quat_z},{sig.IMU.Sacrum.time[i]},{sacrum_accel_x}, {sacrum_accel_y}, {sacrum_accel_z},{sacrum_gyro_x},{sacrum_gyro_y},{sacrum_gyro_z},{sacrum_mag_x},{sacrum_mag_y},{sacrum_mag_z},{sacrum_quat_x},{sacrum_quat_y},{sacrum_quat_z},{sig.time[i]},{grf_x},{grf_y},{grf_z});\n"
    lines.append(insert_query)
College BGI
  • 9
  • 2

1 Answers1

1

When writing SQL statements in Python, you should typically not use string formatting, but use the features of the language for variable substition. Not only does this solve problems like the one you're asking about, but it also prevents unsafe 'SQL injection' problems.

You didn't include in your example how you plan to execute that SQL query, but you say you're using MySQL. An Example:

conn = mysql.connector.connect(user='my_user', database='my_database')
cursor = conn.cursor()
fields = ', '.join('%s' for _ in len(var_list))
cursor.query(f'INSERT INTO %s ({fields}) VALUES ({fields})', (*var_list, *value_list))

This assumes var_list has the names of the columns you're after and value_list has the values, in a list of the same length.

If you provide more details on exactly what you're trying to pass in, the example might change - but the key thing is this, for MySQL, %s will be replaced with a value from the tuple passed along with the query. That's why the example generates a string with that number of %s.

For your issue, if you pass None, the .query() method will replace that with the appropriate value, in the case of MySQL NULL.

Grismar
  • 27,561
  • 4
  • 31
  • 54