0

I have a table called led_status and a Field called "test_led":

mysql> describe led_status;
+------------------------------+------------+------+-----+---------+-------+
| Field                        | Type       | Null | Key | Default | Extra |
+------------------------------+------------+------+-----+---------+-------+
| test_led                     | varchar(5) | NO   |     | NULL    |       |
+------------------------------+------------+------+-----+---------+-------+

I am attempting to enter a string of either "TRUE" or "FALSE" (not int 1 or 0) with the following code:

def write_database_value(table,field,value):
    connect = mysql.connector.connect(user=db_info.username,
                              password=db_info.password,
                              host=db_info.servername,
                              database=db_info.database)
    cursor = connect.cursor()
    cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))
    connect.commit()
    cursor.close()
    connect.close()

def read_database_value(table,field):
    connect = mysql.connector.connect(user=db_info.username,
                              password=db_info.password,
                              host=db_info.servername,
                              database=db_info.database)
    cursor = connect.cursor(buffered=True)
    cursor.execute(("SELECT %s FROM %s") % (field, table))

    for data in cursor:
        database_value = data
        cursor.close()
    connect.close()
    return database_value

Which is called from this script:

def get_led_status(led):
    led_status = read_database_value("led_status", led)
    led_status = (led_status[0])
    return led_status

def is_pool_pump_running():
    pool_running = get_led_status("test_led")
    if pool_running == "TRUE":
        print("Pool Pump is Running, shutting it off now")
        write_database_value("led_status","test_led","FALSE")
    else:
        print("Pool Pump is NOT Running, turning it on now")
        write_database_value("led_status","test_led","TRUE")

However everytime I run the script it changes my "TRUE" to a 1 and my "FALSE" to a 0.

I am switching over from a flat file to a database and all of my code (4000 lines of it so far) uses "TRUE" and "FALSE" so I really don't want to have to rewrite it just to use 1 and 0 as opposed to "TRUE" and "FALSE".

Any thoughts would be greatly appreciated.

Improve this question
4
  • Two important things: (1) you don't use the % string formatting on SQL, it leads to SQL injection vulnerabilities (2) divide data and its representation: 0/1 or in Python False/True are the data used for storing and processing in computers, the strings "TRUE" and "FALSE" are how you present it to humans. Commented Jan 2, 2019 at 5:25
  • Thanks @KlausD. It was my understanding that %s is the correct method and that cursor.execute specifically protects against SQL injection issues. Posts on Stack Exchange pointed me in this direction. Is this information incorrect? Second, as I stated above, I am moving from a flat file that uses true/false representation and I really did not want to rewrite all the code to 0/1 when I moved to a database design, hence the reason I want to stick with true/false as a varchar instead of 0/1 as a bit or tinyint. Commented Jan 2, 2019 at 16:38
  • The important difference is the % between the string and the arguments. It has to be a comma to be safe. Commented Jan 2, 2019 at 17:46
  • @KlausD. AH!!! I was thinking of the wrong %! I will read up more on that and change it. Commented Jan 2, 2019 at 17:50

3 Answers 3

Reset to default
1

MySQL does not have boolean types (true/false), they are automatically interpreted (and stored) as 0/1 (in your schema above, you even clearly state that you want tinyints, not regular 32-bit ints). Nothing you can do about that!

If you want to store the literal value "TRUE" / "FALSE" you should use varchar(5) type of column.

Side note: default value for those columns should be either 0 or 1, OR it should be nullable - you have set their default to NULL and the column not nullable, which can lead to errors down the way.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I am using a varchar(5)! If you look at my table above, test_led is varchar(5) type.
@Richard you were not at the time I wrote the answer to this. It shows in the revisions of your post...
actually the test_led was ALWAYS a varchar(5). It still does not work.
def get_led_status(led): led_status = read_database_value("led_status", led) led_status = (led_status[0]) return led_status
sorry, not sure how to format code in a comment :-)
|
0

As @frsechet answer pointed out, in SQL you only have 0/1 representation of boolean. If you want TRUE/FALSE strings then you need to change the column type to string/char(5).

Other option is, if you are using a common function to fetch/update the column value then you can define value mapping inside that function which converts TRUE/FALSE to 1/0 and vice versa.

Improve this answer

1 Comment

I am using a varchar(5)! If you look at my table above, test_led is varchar(5) type.
0

Thank you to everyone that provided input. After a LOT of digging around I figured out my problem. Apparently, you can't parameterize a table name in a prepared statement which is exactly what I was attempting to do above. POST where I found the answer @Conspicuous Compiler was dead on with that statement.

The problem was in my write_database_value() function. This is the correct function that works as needed:

def write_database_value(table,column,value):
    try:
        connection = mysql.connector.connect(user=db_info.username,
                                      password=db_info.password,
                                      host=db_info.servername,
                                      database=db_info.database)
        cursor = connection.cursor(buffered=True)
        sql_update = "UPDATE " + table + " SET " + column + " = %s"
        cursor.execute(sql_update, (value,))
        connection.commit()
        cursor.close()
        connection.close()
    except Error as error :
        print("Failed to Update record in database: {}".format(error))
    finally:
        if(connection.is_connected()):
            connection.close

The big change:

From: cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))

to this:

sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))
Improve this answer

2 Comments

I was 100% going to say that this morning when reading your code, props for figuring it out yourself. As mentioned in the link in my answer, TRUE in whatever letter case is converted to 1. Your query was "UPDATE led_status SET TRUE", which is read as 1, not as the string "TRUE", because it is not wrapped in quotes. A "select test_led from led_status" would have helped you debug more quickly. Side note: it is recommended to use prepared statements rather than inserting strings into a query, for security reasons but also for the reason you experienced right here.
Example: what if instead of "true" someone wrote something along the lines of "1; drop database;"...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.