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

Question

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.

jhylands · Accepted Answer · 2021-08-24 15:48:57Z

1

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?

answered Aug 24, 2021 at 15:48

jhylands

1,0249 silver badges18 bronze badges

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

Comments

snakecharmerb · Accepted Answer · 2021-08-24 16:45:25Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related