I have a series of reports that I have incoming daily and would like to automate the process. So far I have the emails being pushed to a folder and a python script to format them for key matching within the database and re-writing to a new file. I am stuck on the process for exporting the csv file to the MySQL. Any ideas?
2
-
So you could generate the CSV ready but unable to insert into MySQL?Ahsan– Ahsan2016-12-02 15:17:25 +00:00Commented Dec 2, 2016 at 15:17
-
I want to automate the insert into MySQL. I think I am going to take another route and just use python's MySQL package and add onto the script. thanksantiantiderivative– antiantiderivative2016-12-02 19:11:27 +00:00Commented Dec 2, 2016 at 19:11
Add a comment
|
1 Answer
Automate the insert into mysql from CSV:
Install mysql-python:
pip install mysql-python
The script:
#!/usr/bin/env python
import csv
import MySQLdb
mysql_conn = MySQLdb.connect(host='localhost', user='user',
passwd='password', db='your_db_name')
mysql_cursor = mysql_conn.cursor()
f = open('/path/to/file.csv')
csv_f = csv.reader(f)
for row in csv_f:
mysql_cursor.execute("""INSERT INTO test (col1, col2, col3) VALUES(%s, %s, %s)""",
(row[0], row[1], row[2]))
mysql_conn.commit()
mysql_cursor.close()
#!/usr/bin/env python at the top of the file makes it easy to execute the python file from terminal.
Save this file somewhere like: /home/username/bin/csv_to_mysql.py
Mark it as executable: chmod +x /home/username/bin/csv_to_mysql.py
Then run it directly from terminal: /home/username/bin/csv_to_mysql.py
