CODEWITHSUNDEEP
pythonsqlinputmysql-pythonsanitization
Auti
Auti

Reputation: 23

MySQLdb adding character b infront of strings that have been escaped - Python

I am trying to write a simple Python script to bulk add movie titles into a local database, using the MySQLdb (mysqlclient) package. I am reading the titles from a TSV file. But when go to sanitize the inputs using MySQLdb::escape_string(), I get the character b before my string. I believe this means that SQL is interpreting it as a bit value, but when I go to execute my query I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b'Bowery to Bagdad',1955)' at line 1"

The select statement in question:

INSERT INTO movies (imdb_id, title, release_year) VALUES ('tt0044388',b'Bowery to Bagdad',1955)

def TSV_to_SQL(file_to_open):
    from MySQLdb import _mysql

    db=_mysql.connect(host='localhost', user='root', passwd='', db='tutorialdb', charset='utf8')
    
    q = """SELECT * FROM user_id"""
    # MySQLdb.escape_string()
    # db.query(q)
    # results = db.use_result()
    # print(results.fetch_row(maxrows=0, how=1))
    print("starting?")
    with open(file_to_open, encoding="utf8") as file:
        tsv = csv.reader(file, delimiter="\t")
        count = 0
        for line in tsv:
            if count == 10:
                break
            # print(MySQLdb.escape_string(line[1]))
            statement = "INSERT INTO movies (imdb_id, title, release_year) VALUES ('{imdb_id}',{title},{year})\n".format(
                imdb_id=line[0], title=MySQLdb.escape_string(line[1]), year=line[2])
            # db.query(statement)
            print(statement)
            count = count + 1

I know a simple solution would be to just remove the character b from the start of the string, but I was wondering if there was a more proper way, or if I missed something in documentation.

Upvotes: 1

Views: 719

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55630

It's more common to let the connector perform the escaping automatically, by inserting placeholders in the SQL statement and passing a sequence (conventionally a tuple) of values as the second argument to cursor.execute.

conn = MySQLdb.connect(host='localhost', user='root', passwd='', db='tutorialdb', charset='utf8')
cursor = conn.cursor()

statement = """INSERT INTO movies (imdb_id, title, release_year) VALUES (%s, %s, %s)"""

cursor.execute(statement, (line[0], line[1], line[2]))
conn.commit()

The resulting code is more portable - apart from the connection it will work with all DB-API connectors*. Dropping down to low-level functions like _mysql.connect and escape_string is unusual in Python code (though you are perfectly free to code like this if you want, of course).

* Some connection packages may use a different placeholder instead of %s, but %s seems to be the favoured placeholder for MySQL connector packages.

Upvotes: 0

jhylands
jhylands

Reputation: 1014

The 'b' infront of the string represents that the string is binary encoded rather than a literal string. If you use .encode() you will be able to get what you want. How to convert 'binary string' to normal string in Python3?

Upvotes: 1

Related Questions