Skip to content

Instantly share code, notes, and snippets.

@l34marr
Last active August 31, 2017 13:45
Show Gist options
  • Save l34marr/1e13953ec54527f1f52cc7db795b3e0d to your computer and use it in GitHub Desktop.
Save l34marr/1e13953ec54527f1f52cc7db795b3e0d to your computer and use it in GitHub Desktop.
Read Excel File
from openpyxl import load_workbook
wb = load_workbook('vol3.xlsx')
print wb.get_sheet_names()
#ws = wb.get_sheet_by_name(u'Sheet1')
ws = wb.worksheets[0]
print ws.max_row, ws.max_column
result = []
for row in ws.iter_rows():
try:
event_codes = row[29].value.split(';')
except:
pass
first_2_codes = map(lambda x: x[:2], event_codes)
if '10' in first_2_codes and row[15].value in ['6','7','8']:
#print row
result.append(row)
print len(result)
# MySQL for Python
# sudo apt-get install python-mysqldb
import MySQLdb
import sys
try:
db = MySQLdb.connect(
host = 'localost',
port = 3306,
user = 'root',
passwd = '',
db = 'mydb'
)
except Exception as e:
sys.exit('Database Connection Failed')
# Pandas to MySQL
import pandas as pd
pd.read_excel('myfile.xlsx', sheetname=0)
import MySQLdb
db = MySQLdb.connect(host="localhost", port=3306 , user="myacct", passwd="mypass", db="mydb")
df = pd.read_excel("myfile.xlsx", sheetname=0)
df.to_sql(con=db, name="mytbl", if_exists='replace', flavor='mysql')
# Using Chinese characters might cause error
# UnicodeEncodeError: 'latin-1' codec can't encode characters
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment