我在努力推动我的excel.xlsm表格文件数据到sql php myadmin
这是我的代码,我创建了一个模块,在数据库sql和excel.xlsm表格数据库作为xlsm中的列名称与数据库sql中的列名称不同
下面是我的简单代码from sqlalchemy import create_engine
import pandas as pd
import os
import MySQLdb
engine = create_engine('mysql+mysqldb://root:@localhost/myDB?
charset=utf8mb4&binary_prefix=true', echo=False)
mydir = (os.getcwd()).replace('\\', '/') + '/'
all_data = pd.read_excel(r'' + mydir + 'Governance_Tracker - Copy -
Copy.xlsm'
,header = 1).drop(['#'], axis=1)
all_data.replace('\n','', regex=True)
df = all_data.where((pd.notnull(all_data)), None)
print(df)
for i in range(len(df)):
data_dict ={}
data_dict['site_name'] = df['Site Name'][i]
data_dict['region'] = df['Region'][i]
data_dict['site_type'] = df['Site Type'][i]
data_dict['site_code'] = df['SiteCode'][i]
data_dict['tac_name'] = df['TAC Name'][i]
data_dict['dt_readiness'] = df['DT\nReadiness'][i]
data_dict['rfs'] = df['RFS'][i]
data_dict['rfs_date'] = df['RFS Date'][i]
data_dict['huawei_1st_submission_date'] = df['Huawei 1st submission date
'][i]
data_dict['te_1st_submission_date'] = df['TE 1st Response date '][i]
data_dict['huawei_2nd_submission_date'] = df['Huawei 2nd submission date
'][i]
data_dict['te_2nd_submission_date'] = df['TE 2nd Response date '][i]
data_dict['huawei_3rd_submission_date'] = df['Huawei 3rd submission date
'][i]
data_dict['te_3rd_submission_date'] = df['TE 3rd Response date '][i]
data_dict['acceptance_date_opt'] = df['Acceptance Date(Optimization)'][i]
data_dict['acceptance_date_plan'] = df['Acceptance Date(Planning)'][i]
data_dict['signed_sites'] = df['signed sites'][i]
data_dict['as_built_date'] = df['As Built Date'][i]
data_dict['as_built_status'] = df['AS built status'][i]
data_dict['date_dt'] = df['Date DT'][i]
data_dict['dt_status'] = df['DT Status'][i]
data_dict['shr_status'] = df['SHR Status'][i]
data_dict['dt_planned'] = df['DT Planned'][i]
data_dict['integeration_status'] = df['Integeration Status'][i]
data_dict['comments_snags'] = df['Comments/snags'][i]
data_dict['cluster_name'] = df['Cluster name'][i]
data_dict['type_standalone_colocated'] = df['Type(Standalone/colocated)']
[i]
data_dict['installed_type_standalone_colocated'] = df['Installed type
(Standalone/colocated)'][i]
data_dict['status'] = df[' Status '][i]
data_dict['pending'] = df['Pending '][i]
data_dict['pending_status'] = df['Pending Status'][i]
data_dict['problematic_details'] = df['problematic details'][i]
df1 = pd.DataFrame(data_dict,index=[0])
df1.to_sql('govtracker', con=engine,if_exists='append',index=False)
print("Success")
rows = engine.execute("SELECT * FROM govtracker").fetchall()
print(rows)
但我在终端发现了这些错误
回溯错误
^{pr2}$
这是我在php myadmin中的sql查询CREATE TABLE `mydb`.`govtracker` (
`id` DOUBLE(255, 0) NOT NULL,
`site_name` VARCHAR(255) NOT NULL,
`region` VARCHAR(255) NOT NULL,
`site_type` VARCHAR(255) NOT NULL,
`site_code` VARCHAR(255) NOT NULL,
`tac_name` VARCHAR(255) NOT NULL,
`dt_readiness` DATE NOT NULL,
`rfs` BOOLEAN NOT NULL,
`rfs_date` DATE NOT NULL,
`huawei_1st_submission_date` DATE NOT NULL,
`te_1st_submission_date` DATE NOT NULL,
`huawei_2nd_submission_date` DATE NOT NULL,
`te_2nd_submission_date` DATE NOT NULL,
`huawei_3rd_submission_date` DATE NOT NULL,
`te_3rd_submission_date` DATE NOT NULL,
`acceptance_date_opt` DATE NOT NULL,
`acceptance_date_plan` DATE NOT NULL,
`signed_sites` VARCHAR(255) NOT NULL,
`as_built_date` DATE NOT NULL,
`as_built_status` VARCHAR(255) NOT NULL,
`date_dt` DATE NOT NULL,
`dt_status` VARCHAR(255) NOT NULL,
`shr_status` VARCHAR(255) NOT NULL,
`dt_planned` INT(255) NOT NULL,
`integeration_status` VARCHAR(255) NOT NULL,
`comments_snags` LONGTEXT NOT NULL,
`cluster_name` LONGTEXT NOT NULL,
`type_standalone_colocated` VARCHAR(255) NOT NULL,
`installed_type_standalone_colocated` VARCHAR(255) NOT NULL,
`status` VARCHAR(255) NOT NULL,
`pending` VARCHAR(255) NOT NULL,
`pending_status` LONGTEXT NOT NULL,
`problematic_details` LONGTEXT NOT NULL,
`ets_tac` INT(255) NOT NULL,
`region_r` VARCHAR(255) NOT NULL,
`sf6_signed_date` DATE NOT NULL,
`sf6_signed_comment` LONGTEXT NOT NULL,
`comment_history` LONGTEXT NOT NULL,
`on_air_owner` VARCHAR(255) NOT NULL,
`pp_owner` VARCHAR(255) NOT NULL,
`report_comment` LONGTEXT NOT NULL,
`hu_opt_area_owner` VARCHAR(255) NOT NULL,
`planning_owner` VARCHAR(255) NOT NULL,
`po_number` VARCHAR(255) NOT NULL,
`trigger_date` DATE NOT NULL,
`as_built_status_tr` VARCHAR(255) NOT NULL
) ENGINE = InnoDB;