import pandas as pd
import MySQLdb
from util import rebuild_table, delete_table
import numpy as np
con = MySQLdb.connect(host="127.0.0.1", port=3306, user="******", db="ezlife", charset="utf8")
delete_table("user",con)
path = '/Users/zhouyang/Downloads/20160918/user.csv'
# read from csv file
df = pd.read_csv(path)
# strip every element in columns
columns = list(df)
columns = map(lambda x: x.strip(), columns)
df = df.ix[:, columns]
try:
pd.io.sql.to_sql(df, 'user', con, flavor='mysql', if_exists='append', index=False)
except Exception,e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115", user="******", passwd="yihuo_root", port=3306, charset="utf8",
db="ezlife")
rebuild_table(table_name="user", con=yihuo_con, df=df)
df['create_at'] = df['create_at'].map(lambda x: x
if pd.isnull(x) else transform_date(x))
df['purchase_date'] = df['purchase_date'].map(
lambda x: x if pd.isnull(x) else transform_date(x))
assert (len(df) > 0)
encoding = chardet.detect(df["eplate_description"][0])["encoding"]
df["eplate_description"] = df["eplate_description"].map(
lambda x: x.decode(encoding))
try:
pd.io.sql.to_sql(df,
'eplates',
con,
flavor='mysql',
if_exists='append',
index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115",
user="******",
passwd="yihuo_root",
port=3306,
charset="utf8",
db="ezlife")
rebuild_table(table_name="eplates", con=yihuo_con, df=df)
# -*- coding:utf-8 -*-
# author：zhouyang
import pandas as pd
import MySQLdb
from util import delete_table, rebuild_table
con = MySQLdb.connect(host="127.0.0.1", port=3306, user="******", db="ezlife", charset="utf8")
# delete before insert
delete_table("efirms", con)
df = pd.read_csv("/Users/zhouyang/Downloads/20160918/machine.csv")
try:
pd.io.sql.to_sql(df, 'efirms', con, flavor='mysql', if_exists='append', index=False)
except Exception,e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115", user="******", passwd="yihuo_root", port=3306, charset="utf8",
db="ezlife")
rebuild_table(table_name="efirms", con=yihuo_con, df=df)
def transform_date(date_str):
if re.match("\d{4}-\d{2}-\d{2}-\d{2}-\d{2}-\d{2}", date_str) is None:
print 'date string is not right please check it later!'
return np.nan
return datetime.strptime(date_str, "%Y-%m-%d-%H-%M-%S")
df['date'] = df['date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
try:
pd.io.sql.to_sql(df,
'test_result',
con,
flavor='mysql',
if_exists='append',
index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115",
user="******",
passwd="yihuo_root",
port=3306,
charset="utf8",
db="ezlife")
rebuild_table(table_name="test_result", con=yihuo_con, df=df)
del df['efield_list_str']
efield_cycles = "step%d_efield_cycles" % i
df[efield_cycles] = df['efield_str'].map(lambda x: get_cycles(x))
del df['efield_str']
incubation = "step%d_incubation_duration" % i
df[incubation] = df[step].map(lambda x: get_number(get_incubation(x)))
wash = "step%d_wash" % i
df[wash] = df[step].map(lambda x: get_wash_str(x))
del df[step]
try:
pd.io.sql.to_sql(df,
'sop',
con,
flavor='mysql',
if_exists='append',
index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115",
user="******",
passwd="yihuo_root",
port=3306,
charset="utf8",
db="ezlife")
rebuild_table(table_name="sop", con=yihuo_con, df=df)
return datetime.strptime(date_str, "%Y-%m-%d-%H-%M-%S")
df['purchase_date'] = df['purchase_date'].map(
lambda x: x if pd.isnull(x) else transform_date(x))
df['arrival_date'] = df['arrival_date'].map(
lambda x: x if pd.isnull(x) else transform_date(x))
df['create_date'] = df['create_date'].map(
lambda x: x if pd.isnull(x) else transform_date(x))
try:
pd.io.sql.to_sql(df,
'sample',
con,
flavor='mysql',
if_exists='append',
index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115",
user="******",
passwd="yihuo_root",
port=3306,
charset="utf8",
db="ezlife")
rebuild_table(table_name="sample", con=yihuo_con, df=df)
con = MySQLdb.connect(host="127.0.0.1",
port=3306,
user="******",
db="ezlife",
charset="utf8")
# delete before insert
delete_table("efirms", con)
df = pd.read_csv("/Users/zhouyang/Downloads/20160918/machine.csv")
try:
pd.io.sql.to_sql(df,
'efirms',
con,
flavor='mysql',
if_exists='append',
index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115",
user="******",
passwd="yihuo_root",
port=3306,
charset="utf8",
db="ezlife")
rebuild_table(table_name="efirms", con=yihuo_con, df=df)
df = change_encode(df,"supplier")
df = change_encode(df,"attributes")
# 将日期字符串转换为datetime数据类型
def transform_date(date_str):
if re.match("\d{4}-\d{2}-\d{2}-\d{2}-\d{2}-\d{2}", date_str) is None:
print 'date string is not right please check it later!'
return np.nan
return datetime.strptime(date_str, "%Y-%m-%d-%H-%M-%S")
df['purchase_date'] = df['purchase_date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
df['arrival_date'] = df['arrival_date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
df['create_date'] = df['create_date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
try:
pd.io.sql.to_sql(df, 'reagent', con, flavor='mysql', if_exists='append', index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# 此处表结构有变化,需要使用navicat进行数据同步
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115", user="******", passwd="yihuo_root", port=3306, charset="utf8",
db="ezlife")
rebuild_table(table_name="reagent", con=yihuo_con, df=df)
df['purchase_date'] = df['purchase_date'].map(
lambda x: x if pd.isnull(x) else transform_date(x))
df['arrival_date'] = df['arrival_date'].map(
lambda x: x if pd.isnull(x) else transform_date(x))
df['create_date'] = df['create_date'].map(
lambda x: x if pd.isnull(x) else transform_date(x))
try:
pd.io.sql.to_sql(df,
'reagent',
con,
flavor='mysql',
if_exists='append',
index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# 此处表结构有变化,需要使用navicat进行数据同步
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115",
user="******",
passwd="yihuo_root",
port=3306,
charset="utf8",
db="ezlife")
rebuild_table(table_name="reagent", con=yihuo_con, df=df)
del df['efield_list_len']
efield_voltage1 = "step%d_efield_vol1_voltage" % i
efield_duration1 = "step%d_efield_vol1_duration" % i
efield_voltage2 = "step%d_efield_vol2_voltage" % i
efield_duration2 = "step%d_efield_vol2_duration" % i
df[efield_voltage1] = df['efield_list_str'].map(lambda x: get_efield_voltage1(x))
df[efield_duration1] = df['efield_list_str'].map(lambda x: get_efield_duration1(x))
df[efield_voltage2] = df['efield_list_str'].map(lambda x: get_efield_voltage2(x))
df[efield_duration2] = df['efield_list_str'].map(lambda x: get_efield_duration2(x))
del df['efield_list_str']
efield_cycles = "step%d_efield_cycles" % i
df[efield_cycles] = df['efield_str'].map(lambda x: get_cycles(x))
del df['efield_str']
incubation = "step%d_incubation_duration" % i
df[incubation] = df[step].map(lambda x: get_number(get_incubation(x)))
wash = "step%d_wash" % i
df[wash] = df[step].map(lambda x: get_wash_str(x))
del df[step]
try:
pd.io.sql.to_sql(df, 'sop', con, flavor='mysql', if_exists='append', index=False)
except Exception,e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115", user="******", passwd="yihuo_root", port=3306, charset="utf8",
db="ezlife")
rebuild_table(table_name="sop", con=yihuo_con, df=df)
path = '/Users/zhouyang/Downloads/20160918/user.csv'
# read from csv file
df = pd.read_csv(path)
# strip every element in columns
columns = list(df)
columns = map(lambda x: x.strip(), columns)
df = df.ix[:, columns]
try:
pd.io.sql.to_sql(df,
'user',
con,
flavor='mysql',
if_exists='append',
index=False)
except Exception, e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115",
user="******",
passwd="yihuo_root",
port=3306,
charset="utf8",
db="ezlife")
rebuild_table(table_name="user", con=yihuo_con, df=df)
year = int(x[:4])
month = int(x[4:6])
# 月份出现了0,非法!
if month <= 0:
month = 1
day = int(x[6:8])
# 日期出现了0,非法!
if day <= 0:
day = 1
hour = int(x[8:10])
min = int(x[10:12])
second = int(x[12:])
return datetime(year=year, month=month, day=day, hour=hour, minute=min, second=second)
df['create_at'] = df['create_at'].map(lambda x: x if pd.isnull(x) else str(int(x)))
df['create_at'] = df['create_at'].map(lambda x: x if pd.isnull(x) else transform_date(x))
# pandas插入可以省略一些空的列
try:
pd.io.sql.to_sql(df, 'project', con, flavor='mysql', if_exists='append', index=False)
except Exception,e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115", user="******", passwd="yihuo_root", port=3306, charset="utf8",
db="ezlife")
rebuild_table(table_name="project", con=yihuo_con, df=df)
df = change_encode(df,"name")
# 删除可能多出的列
# columns = list(df)
# columns = columns[:19]
# df = df.ix[:, columns]
# 将日期字符串转换为datetime数据类型
def transform_date(date_str):
if re.match("\d{4}-\d{2}-\d{2}-\d{2}-\d{2}-\d{2}", date_str) is None:
print 'date string is not right please check it later!'
return np.nan
return datetime.strptime(date_str, "%Y-%m-%d-%H-%M-%S")
df['purchase_date'] = df['purchase_date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
df['arrival_date'] = df['arrival_date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
df['create_date'] = df['create_date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
try:
pd.io.sql.to_sql(df, 'sample', con, flavor='mysql', if_exists='append', index=False)
except Exception,e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115", user="******", passwd="yihuo_root", port=3306, charset="utf8",
db="ezlife")
rebuild_table(table_name="sample", con=yihuo_con, df=df)
df = pd.read_csv(path)
# 更换列的名称
df = df.rename(columns={"eplate_types_id": "eplate_type_id"})
def transform_date(date_str):
if re.match("\d{4}-\d{2}-\d{2}-\d{2}-\d{2}-\d{2}", date_str) is None:
print 'date string is not right please check it later!'
return np.nan
return datetime.strptime(date_str, "%Y-%m-%d-%H-%M-%S")
df['create_at'] = df['create_at'].map(lambda x: x if pd.isnull(x) else transform_date(x))
df['purchase_date'] = df['purchase_date'].map(lambda x: x if pd.isnull(x) else transform_date(x))
assert(len(df)>0)
encoding = chardet.detect(df["eplate_description"][0])["encoding"]
df["eplate_description"] = df["eplate_description"].map(lambda x:x.decode(encoding))
try:
pd.io.sql.to_sql(df, 'eplates', con, flavor='mysql', if_exists='append', index=False)
except Exception,e:
print e
print 'there is an error, please fix it before continue!'
exit(-1)
# transfer data to remote mysql server
yihuo_con = MySQLdb.connect(host="52.192.115.115", user="******", passwd="yihuo_root", port=3306, charset="utf8",
db="ezlife")
rebuild_table(table_name="eplates", con=yihuo_con, df=df)