![]() Note :if you don't connect then, please install the mysql-connector-python package, type the following command: pip install mysql-connector-python Print("Error while connecting to MySQL", e) Step 3 : Connect to the MySQL using Python and create a DatabaseĬreate a connection object to connect to MySQL, The connect() constructor creates a connection to the MySQL and returns a MySQLConnection object.Ĭonn = nnect(host='localhost', ur username, passwordĬursor.execute("CREATE DATABASE employee") You'll need to change the path name to reflect the location where the CSV file is stored on your computerĮmpdata = pd.read_csv('C:\\Users\\XXXXX\\empdata.csv', index_col=False, delimiter = ',') Here is the code that I used to import the CSV file, and then create the DataFrame. Next, import the CSV file into Python using the pandas library. Note: the above employee csv data is taken from the below link employee_data Step 2: Import the CSV File into the DataFrame. For example, I prepared a simple CSV file with the following data: To begin, prepare the CSV file that you'd like to import to MySQL. ![]() Step 4: Create a table and Import the CSV data into the MySQL table.Step 3 : Connect to the MySQL using Python and create a Database.Step 2: Import the CSV File into the DataFrame.Avoid using double quotes around string literals, to make your SQL code work even when ANSI_QUOTES is set. The takeaway: use backtick characters to escape identifiers (column names, table names, etc.) and use single quotes around string literals. And using single quotes around identifiers shouldn't ever work. If sql_mode does not include ANSI_QUOTES (either explicitly or included in some other setting), then the double quotes around identifers doesn't work. "Rec Open Date" DATE NOT NULL COMMENT 'Rec Open Date', If sql_mode variable includes ANSI_QUOTES, then you can also use double quotes to escape identifiers. On my keyboard, the backtick is the key at the upper left ` ~ just to the left of the 1 ! key. `Rec Open Date` DATE NOT NULL COMMENT 'Rec Open Date', To use an identifier that includes invalid characters, like a space, the identifier must be escaped. In MySQL, identifiers (column names, table names) can be escaped using backtick characters. When there is a header line, we typically "skip over" it using IGNORE 1 LINES.Īs an aside, I prefer to use the keyword FIELDS in the LOAD DATA statement in place of COLUMNS. (It's possible that some other component of your system has that type of requirement, but MySQL LOAD DATA doesn't.) MySQL doesn't have any requirement about the "names" of the fields (from the header line in the. The MySQL LOAD DATA statement loads values from the file by position, not by name. LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE test1234 COLUMNS TERMINATED BY ',' > Rec_Open_Date Date NOT NULL COMMENT 'Rec Open Date',įollowing EG3, creating the table I would then do the following, but the column headers in the file would have to be the same as the ones on the datatable, as I understand it. ![]() > "Rec Open Date" Date NOT NULL COMMENT 'Rec Open Date',ĮRROR 1064 (42000): You have an error in your SQL syntax check the manual that corresponds to your MySQL server version for the right syntax to use near '"Rec Open Date" Date NOT NULL COMMENT 'Rec Open Date',ĮG3: mysql> CREATE TABLE IF NOT EXISTS test1234 ( > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ĮRROR 1064 (42000): You have an error in your SQL syntax check the manual that corresponds to your MySQL server version for the right syntax to use near ''Rec Open Date' Date NOT NULL COMMENT 'Rec Open Date',ĮG2 mysql> ^MCREATE TABLE IF NOT EXISTS test1234 ( > 'Rec Open Date' Date NOT NULL COMMENT 'Rec Open Date', > id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', ![]() This seems to be the best option for me, but can anyone advise other wise? Ideally I would like the csv file column headers to be the same as the datatable headers and EG3 seems to be the way to achieve this.ĮG1: mysql> CREATE TABLE IF NOT EXISTS test1234 ( replace the spaces with underscores in the table header. But this would involve changing the column header names in the csv file i.e. The way I can get this to work is to change replace the spaces with underscores in the table header i.e. Now I would like the column header in the table to be the same as the csv file, but I can't get this to work using ' or ", shown below in EG1 and EG2. The file.csv I am trying to import looks like this(but it only has 1 column for this example "Rec Open Date"): "Rec Open Date", ![]() I am trying to use the LOAD DATA INFILE but first I have to create the table and this is where my issues is. I have a csv file that I would like to import into mysql, and I am slowly getting there. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |