# -*- coding:utf-8 -*-
# author：zhouyang
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",
import MySQLdb
import numpy as np
import re
from util import rebuild_table, delete_table
import chardet
# fixme: 表结构有变动,在和服务器同步之前,请先同步表结构
# 删除了列barcode,和列bar_code重复
con = MySQLdb.connect(host="127.0.0.1",
port=3306,
user="******",
db="ezlife",
charset="utf8")
# 删除原来表的内容
delete_table("eplates", con)
path = "/Users/zhouyang/Downloads/20160918/eplate.csv"
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
# -*- 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)
import pandas as pd
import MySQLdb
import re
from datetime import datetime
import numpy as np
from util import rebuild_table, delete_table
con = MySQLdb.connect(host="127.0.0.1",
port=3306,
user="******",
db="ezlife",
charset="utf8")
# 删除原来表的内容
delete_table("test_result", con)
path = "/Users/zhouyang/Downloads/20160918/test_result.csv"
df = pd.read_csv(path)
rename_dict = {
"SOP_id": "sop_id",
"amp": "current_amp",
"stdwell": "std_well",
"cvwell": "cv_well",
"Ispositive": "is_positive"
}
df = df.rename(columns=rename_dict)
def get_mix_str(sample_str, index):
# author：zhouyang
import pandas as pd
import MySQLdb
import re
import numpy as np
from util import rebuild_table, delete_table
con = MySQLdb.connect(host="127.0.0.1",
port=3306,
user="******",
db="ezlife",
charset="utf8")
# 删除原来表的内容
delete_table("sop", con)
path = "/Users/zhouyang/Downloads/20160918/sop.csv"
df = pd.read_csv(path)
# 重命名不符合规范的列
# 简化列名
rename_dict = {
"SOP_id": "sop_id",
"SOP_name": "sop_name",
"Polymerization-Instructions": "step_1",
"Sample-Instructions": "step_2",
"Detector-Instructions": "step_3",
"Reporter-Instructions": "step_4",
"Readout-Instructions": "step_5"
}
df = df.rename(columns=rename_dict)
import MySQLdb
import pandas as pd
from datetime import datetime
import numpy as np
from util import rebuild_table, delete_table
import re
import chardet
con = MySQLdb.connect(host="127.0.0.1",
port=3306,
user="******",
db="ezlife",
charset="utf8")
# 删除原来表的内容
delete_table("sample", con)
df = pd.read_csv("/Users/zhouyang/Downloads/20160918/sample.csv")
# 需要更换列名的列,及更换之后的列名对应关系
rename_dict = {
"OD_or_amount": "od_or_amount",
"Purification": "purification",
"5_mod": "mod_5",
"3_mod": "mod_3",
"purchase_data": "purchase_date",
"arrival_data": "arrival_date",
"created_data": "create_date"
}
df = df.rename(columns=rename_dict)
# -*- 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="******",
# -*- coding:utf-8 -*-
# author：zhouyang
import pandas as pd
import MySQLdb
from datetime import datetime
import numpy as np
from util import delete_table, rebuild_table
import re
import chardet
con = MySQLdb.connect(host="127.0.0.1", port=3306, user="******", db="ezlife", charset="utf8")
# 删除原来表的内容
delete_table("reagent", con)
df = pd.read_csv("/Users/zhouyang/Downloads/20160918/reagent.csv")
# 需要更换列名的列,及更换之后的列名对应关系
rename_dict = {
"purchase_data": "purchase_date",
"arrival_data": "arrival_date",
"created_data": "create_date"
}
df = df.rename(columns=rename_dict)
def change_encode(df,column):
for i in df.index:
value = df[column][i]
if pd.isnull(value):
import pandas as pd
import MySQLdb
from datetime import datetime
import numpy as np
from util import delete_table, rebuild_table
import re
import chardet
con = MySQLdb.connect(host="127.0.0.1",
port=3306,
user="******",
db="ezlife",
charset="utf8")
# 删除原来表的内容
delete_table("reagent", con)
df = pd.read_csv("/Users/zhouyang/Downloads/20160918/reagent.csv")
# 需要更换列名的列,及更换之后的列名对应关系
rename_dict = {
"purchase_data": "purchase_date",
"arrival_data": "arrival_date",
"created_data": "create_date"
}
df = df.rename(columns=rename_dict)
def change_encode(df, column):
for i in df.index:
value = df[column][i]
# -*- coding:utf-8 -*-
# author：zhouyang
import pandas as pd
import MySQLdb
import re
import numpy as np
from util import rebuild_table,delete_table
con = MySQLdb.connect(host="127.0.0.1", port=3306, user="******", db="ezlife", charset="utf8")
# 删除原来表的内容
delete_table("sop",con)
path = "/Users/zhouyang/Downloads/20160918/sop.csv"
df = pd.read_csv(path)
# 重命名不符合规范的列
# 简化列名
rename_dict = {
"SOP_id": "sop_id",
"SOP_name": "sop_name",
"Polymerization-Instructions": "step_1",
"Sample-Instructions": "step_2",
"Detector-Instructions": "step_3",
"Reporter-Instructions": "step_4",
"Readout-Instructions": "step_5"
}
df = df.rename(columns=rename_dict)
# 获取mix(str)
# -*- coding:utf-8 -*-
# author：zhouyang
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',
# -*- coding:utf-8 -*-
# author：zhouyang
import pandas as pd
from datetime import datetime
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_table('project', con)
# 因为含有中文,需要指定编码格式
df = pd.read_csv("/Users/zhouyang/Downloads/20160918/project.csv", encoding="gbk")
columns = ["_id", "catalogA", "catalogB", "catalogC", "attributes", "description", "create_at", "Mongodb_ID"]
df.columns = columns
# 将日期字符串转换为datetime数据类型
def transform_date(x):
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])
# -*- coding:utf-8 -*-
# author：zhouyang
import MySQLdb
import pandas as pd
from datetime import datetime
import numpy as np
from util import rebuild_table,delete_table
import re
import chardet
con = MySQLdb.connect(host="127.0.0.1", port=3306, user="******", db="ezlife", charset="utf8")
# 删除原来表的内容
delete_table("sample",con)
df = pd.read_csv("/Users/zhouyang/Downloads/20160918/sample.csv")
# 需要更换列名的列,及更换之后的列名对应关系
rename_dict = {
"OD_or_amount": "od_or_amount",
"Purification": "purification",
"5_mod": "mod_5",
"3_mod": "mod_3",
"purchase_data": "purchase_date",
"arrival_data": "arrival_date",
"created_data": "create_date"
}
df = df.rename(columns=rename_dict)
# -*- coding:utf-8 -*-
# author：zhouyang
import pandas as pd
from datetime import datetime
import MySQLdb
import numpy as np
import re
from util import rebuild_table, delete_table
import chardet
# fixme: 表结构有变动,在和服务器同步之前,请先同步表结构
# 删除了列barcode,和列bar_code重复
con = MySQLdb.connect(host="127.0.0.1", port=3306, user="******", db="ezlife", charset="utf8")
# 删除原来表的内容
delete_table("eplates", con)
path = "/Users/zhouyang/Downloads/20160918/eplate.csv"
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))