Error

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

When

While loading data in table from local CSV file using python and MySql.



import mysql.connector as mysql
import csv


def connect(db_name):
try:
return mysql.connect(user='root',password='anish@123',host='localhost',database=db_name,allow_local_infile=True)
except Error as e:
print(e)


if __name__=='__main__':
connection=connect('sales')
cursor=connection.cursor()

cursor.execute("drop table if exists salesperson")
cursor.execute('''create table salesperson(
id int(10) not null auto_increment,
Country varchar(100),
Item_Type varchar(100),
Order_Date varchar(100),
Units_Sold int(10),
Unit_Cost int(10),
primary key (id)
)''')


q='''load data local infile
"\Users\anish\Sales_Records.csv"
into table salesperson fields terminated by ',' enclosed by '"'
(Country,Item_Type,Order_Date,Units_Sold,Unit_Cost);
'''

cursor.execute(q)
connection.commit()
cursor.execute('select * from salesperson limit 10')
print(cursor.fetchall())
connection.close()
    
    

 Where is issue

The issue is at "\Users\anish\Sales_Records.csv". \U starts an eight-character Unicode escape which causes the error.

What did I try

1>"C:\Users\anish\Sales_Records.csv"

Result:-Failed

2>"C:\\Users\\anish\\Sales_Records.csv"

Result

---------------------------------------------------------------------------
MySQLInterfaceError                       Traceback (most recent call last)
F:\python\Conda\lib\site-packages\mysql\connector\connection_cext.py in cmd_query(self, query, raw, buffered, raw_as_string)
    507                                raw=raw, buffered=buffered,
--> 508                                raw_as_string=raw_as_string)
    509         except MySQLInterfaceError as exc:

MySQLInterfaceError: File 'C:UsersanishSales_Records.csv' not found (OS errno 2 - No such file or directory)

DatabaseError: 2 (HY000): File 'C:UsersanishSales_Records.csv' not found (OS errno 2 - No such file or directory)

Solution

I have used four backslash

import mysql.connector as mysql
import csv

def connect(db_name):
    try:
        return mysql.connect(user='root',password='anish@123',host='localhost',database=db_name,allow_local_infile=True)
    except Error as e:
        print(e)

if __name__=='__main__':
    connection=connect('sales')
    cursor=connection.cursor()
    
    cursor.execute("drop table if exists salesperson")
    cursor.execute('''create table salesperson(
    id int(10) not null auto_increment,
    Country varchar(100), 
    Item_Type varchar(100),
    Order_Date varchar(100),
    Units_Sold int(10),
    Unit_Cost int(10),
    primary key (id)
    )''')
    
    
    q='''load data local infile
    "C:\\\\Users\\\\anish\\\\Sales_Records.csv"
    into table salesperson fields terminated by ',' enclosed by '"'
    (Country,Item_Type,Order_Date,Units_Sold,Unit_Cost);
    '''
    
    cursor.execute(q)
    connection.commit()
    cursor.execute('select * from salesperson limit 10')
    print(cursor.fetchall())
    connection.close()
                
    
    
    

Result

[(1, 'Tuvalu', 'Baby Food', '5/28/2010', 9925, 159), (2, 'Grenada', 'Cereal', '8/22/2012', 2804, 117), (3, 'Russia', 'Office Supplies', '5/2/2014', 1779, 525), (4, 'Sao Tome and Principe', 'Fruits', '6/20/2014', 8102, 7), (5, 'Rwanda', 'Office Supplies', '2/1/2013', 5062, 525), (6, 'Solomon Islands', 'Baby Food', '2/4/2015', 2974, 159), (7, 'Angola', 'Household', '4/23/2011', 4187, 503), (8, 'Burkina Faso', 'Vegetables', '7/17/2012', 8082, 91), (9, 'Republic of the Congo', 'Personal Care', '7/14/2015', 6070, 57), (10, 'Senegal', 'Cereal', '4/18/2014', 6593, 117)]