![]() ![]() # in the below line please pass the create table statement which you want #to createĬursor. Print("You're connected to database: ", record)Ĭursor.execute('DROP TABLE IF EXISTS employee_data ') ![]() We will create an employee_data table under the employee database and insert the records in MySQL with below python code.īuild Professional SQL Projects for Data Analysis with ProjectProĬonn = nnect(host='localhost', database='employee', user='root', conn.is_connected(): Step 4: Create a table and Import the CSV data into the MySQL table Output of the above code: After running the above the code will create an employee database in mysql as shown in below. Note :if you don't connect then, please install the mysql-connector-python package, type the following command: pip install mysql-connector-python Considering this is only a development, so I'm not concerned by the real performance. Will it continue to import until the size of the table will reach 7GB Note: The data directory of the database in on an external HDD. Extract Data from Microsoft Access Save files as CSV Connect toMySQL server Run one SQL file that exports all the CSVs using LOAD DATA LOCAL INFILE Ive written the entire SQL file that DROPs each table then re-uploads it and this runs like a dream in MySQL WorkBench. The size of the imported table in MySQL is at the moment 1.5GB (data indexes). Print("Error while connecting to MySQL", e) It has been importing it for 6 hours already. 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.This is the script that contains the data definition statements for the sakila database. Otherwise, the table must be created on the same MySQL server instance into which it is being imported. Find and import the sakila-schema.sql file. When importing a table from another MySQL server instance, both MySQL server instances must have General Availability (GA) status and must be the same version. Step 2: Import the CSV File into the DataFrame. Open MySQL Workbench, select the models view from the sidebar in the home screen, click ( >) next to Models, and then click Reverse Engineer MySQL Create Script.Hope this helps someone given the fact it wasn't easily answered. Also take note that you'll have to constantly have FTP access enabled or enabled from your IP Address as many hosts like this locked down these days. The only thing you will need to ensure is that the MySQL server you are scripting to allows the use of LOAD DATA LOCAL INFILE as some restrict this on shared servers. The VBA I used to save to FTP: ThisWorkbook.SaveAs for the responses to the original question to those that tried to help but as you can see I needed a solution that required no extra input from the user, one button does all. Here's the PHP (ensure MyTable.csv is in the same folder as the PHP file else modify below): I'm no natural born coder, I find solutions to what I need to achieve and here's what I have working. ![]() Having researched and tested out a few ideas I have now found a solution.ġ) Access or Excel and utilise VBA to Save the CSV files to the FTP of where your website is.Ģ) After the SaveAs VBA statement I then had another line of code that fires open a web browser which loads a PHP page and executes a INSERT with LOAD DATA LOCAL INFILE.Įven with a 50mb CSV file the saving process over BT Infinity only takes a minute or so and the PHP runs in seconds. ![]()
0 Comments
Leave a Reply. |