数据库操作-表合并-Python连接Mysql-批量修改列属性-csv读入Python

import MySQLdb
import pandas as pd
from sqlalchemy import create_engine
import os

def read_file(filepath):
    pathDir =  os.listdir(filepath)
    list = []
    for allDir in pathDir:
        child = os.path.join('%s/%s' % (filepath, allDir))
        list.append(child)
    return list


def merge_table():
    conn= MySQLdb.connect(
            host='localhost',
            port = 3306,
            user='root',
            passwd='123456',
            db ='test'
            )
    cur = conn.cursor()
    
    select_table = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'python'"
    cur.execute(select_table)
    flag = 0
    for field_desc in cur:
        
        sql_delect = "alter table python."+field_desc[0]+" drop column `index`"
        sql_change = "alter table python."+field_desc[0]+" change secID secID"+str(flag)+" VARCHAR(30);"
        flag+=1
        try:
            cur.execute(sql_delect)
        except:
            pass
        try:
            cur.execute(sql_change)
        except:
            pass
    cur.execute(select_table)
    flag1 = 0
    count = 0
    for field_desc in cur:
        print(field_desc[0])
        if(count==0):
            sql_merge = "CREATE table python.temp"+str(count)+" (SELECT * FROM python."+field_desc[0]+")"
            cur.execute(sql_merge)
        else:
            lasttable = "temp"+str(count-1)
            nowtable = field_desc[0]
            sql_merge = "CREATE table python.temp"+str(count)+" (SELECT * FROM python."+lasttable+" LEFT JOIN python."+nowtable+" ON python."+lasttable+".secID"+str(count-1)+" = python."+nowtable+".secID"+str(count)+" )"
            print(sql_merge)
            cur.execute(sql_merge)
        count+=1
        
    for i in range(1,flag):
        sql_delect = "alter table python.temp"+str(count-1)+" drop column secID"+str(i)
        
def csv_tosql(path,newname):
    engine = create_engine('mysql://root:123456@localhost:3306/test')
    #df = pd.read_csv("F:/mysql/PB12.csv")
    df = pd.read_csv(path)
    #schema means the name of DataBase
    df.to_sql(newname, engine, schema='python', if_exists='append')
    
    
    
def alter_table(table):
    conn= MySQLdb.connect(
            host='localhost',
            port = 3306,
            user='root',
            passwd='123456',
            db ='test',
            )
    cur = conn.cursor()
    query = "select * from "+table
    cur.execute(query)
    for field_desc in cur.description:
        print(field_desc[0])
        query2 = "alter table "+table+" modify `"+field_desc[0]+"` varchar(30)"
        cur.execute(query2)
    cur.close()
    conn.close()
#csv_tosql("E:/slowsnowball/PB/pb14.csv",'pb14')
list = read_file("E:/slowsnowball/PB")
for path in list:
    csv = path.split('/')[-1]
    print(csv)
    csv_tosql(path,csv)
merge_table()

　　

转载于:https://www.cnblogs.com/yunerlalala/p/6613077.html