How to trim white space in MySQLdb using python

Question

I have a web form taking user data and putting it into a mysql database. I need to trim leading and trailing blanks/spaces. I currently am utilizing the strip() method, but it only trims them for the fields, NOT for the mysql database.

My code is:

first_name = first_name.strip()
last_name = last_name.strip()

so on and so forth. It strips it perfectly fine for the webpage, but not when it is entered into the SQL database. The spaces still exist. How do I remove them?

EDIT:

        db = MySQLdb.connect("localhost","user","pass","db_name")
        cursor = db.cursor()
                cursor.execute("Select * FROM registrants")
                cursor.execute("INSERT INTO registrants VALUES( " + "'" + first_name + "'" + ", " + "'" + last_name + "'" + ");")
        db.commit()
        db.close()

please clarify your question, how do you insert data into the database? is the data already there? — tback, Dec 05 '12 at 17:35
Could you paste the code that inserts them into the database? — oathead, Dec 05 '12 at 17:39

score 0 · Answer 1 · answered Dec 05 '12 at 18:19

I think you should be passing the values into the INSERT differently

cursor.execute("INSERT INTO registrants (fname, lname) VALUES (%s, %s)", (first_name, last_name)

I'm not sure if that's where you're getting the whitespace, but it opens you up to sql injection so it's bad form to put the variables straight into the query.

score 0 · Accepted Answer · answered Dec 05 '12 at 19:07

0

It could be a scope issue.

If the stripping occurs in a different scope (ex: first_name is a global variable and the strip() occurs in a function) then you will not benefit from it in another scope (if the insert is happening in another function for example).

Have you tried this for a test:

cursor.execute("INSERT INTO registrants VALUES( " + "'" + first_name.strip() + "'" + ", " + "'" + last_name.strip() + "'" + ");")

btw, how do you know there's a space in the db? There could be an issue with the way the data is retrieved or displayed..

answered Dec 05 '12 at 19:07

Jeff H

138
1
6

Because when you look in the database it shows " information" if spaces are entered. And because I only do it once, it's not in a function :). – Michael Ferro Dec 05 '12 at 19:24
Can you do a SELECT LENGTH(first_name) and compare to the actual length of the first name? There could be a mysql client display issue. – Jeff H Dec 05 '12 at 19:31

How to trim white space in MySQLdb using python

2 Answers2