Escaping backslash while inserting in sqlite using Python

Question

I am a beginner in Python. I am trying to insert the following data in sqlite db using Python 3.4.

('config.xml', '09/12/2017 10:33:55 PM', 466, 'C:Users\ron\Downloads\folder');

But I am getting an error

SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 23-26: truncated \UXXXXXXXX escape

I think this is because of this character - \

How can I tell sqlite to escape this character.

Code --

def create_filedetail(conn, filedetail):
    """
    Create a new file detail into the filedetail table
    :param conn:
    :param filedetail:
    :return: filedetail id
    """
    sql = ''' INSERT INTO filedetail(filename,modified_date,filesize,filepath)
              VALUES(?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, filedetail)
    return cur.lastrowid

def main():
    database = r"C:\Users\ron\Documents\sqlitedb\filedb.db"


sql_create_file_detail_table = """ CREATE TABLE IF NOT EXISTS filedetail (
                                    id integer PRIMARY KEY,
                                    filename text NOT NULL,
                                    modified_date text,
                                    filesize integer,
                                    filepath text
                                ); """
conn = create_connection(database)

    if conn is not None:
        # create filedetail table
        create_table(conn, sql_create_file_detail_table)
        with conn:
            # create a new filedetail
            filedetail = ('config.xml', '09/12/2017 10:33:55 PM', 466, "C:Users\ron\Downloads\folder");
            filedetail_id = create_filedetail(conn, filedetail)

    else:
        print("Error! cannot create the database connection.")

Any help is highly appreciated. Thanks in advance.

Probably by creating statements in the correct way; I'm going to take a guess that you're using `.format()` or `%` to build this? Do not do that. You haven't shown your code. — roganjosh, Sep 20 '17 at 17:21
Can you share your code and how you are building the query string? Are you using parameterized statements when you call execute on your cursor? — Kyle, Sep 20 '17 at 17:21

score 1 · Accepted Answer · answered Sep 20 '17 at 17:48

1

You can see that there is a similar issue in this post:

What exactly do "u" and "r" string flags do, and what are raw string literals?

Basically, you can create a string literal by prepending an r to your string.

Look at this example. It returns an invalid character:

>>> mypath = "C:Users\ron\Downloads\folder"
>>> mypath
'C:Users\ron\\Downloads\x0colder'

However, if you use the string literals:

>>> mypath = r"C:Users\ron\Downloads\folder"
>>> mypath
'C:Users\\ron\\Downloads\\folder'

You can then insert this new string into your SQL table.

answered Sep 20 '17 at 17:48

Martin Castro Alvarez

463
3
11

Brilliant. Thanks a lot Martin for your help. – Prithviraj Mitra Sep 20 '17 at 17:50
With the `r`, backslashes are treated as literal...Thanks once again for the SO link – Prithviraj Mitra Sep 20 '17 at 17:56

Escaping backslash while inserting in sqlite using Python

1 Answers1