Python delete_table示例

示例#1

文件： user.py 项目： zhoudayang/read_csv_file

# -*- 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",

示例#2

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

示例#3

文件： efirms.py 项目： zhoudayang/read_csv_file

# -*- 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)

示例#4

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):

示例#5

# 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)

示例#6

文件： sample.py 项目： zion302/read_csv_file

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)

示例#7

# -*- 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="******",

示例#8

文件： reagent.py 项目： zhoudayang/read_csv_file

# -*- 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):

示例#9

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]

示例#10

文件： sop.py 项目： zhoudayang/read_csv_file

# -*- 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)

示例#11

文件： user.py 项目： zion302/read_csv_file

# -*- 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',

示例#12

文件： project.py 项目： zion302/read_csv_file

# -*- 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])

示例#13

文件： sample.py 项目： zhoudayang/read_csv_file

# -*- 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)

示例#14

文件： eplate.py 项目： zhoudayang/read_csv_file

# -*- 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))