我对python和sqlite都很陌生,所以请耐心等待,因为我的方法当然是非常自举的!我尝试使用python3.6将多个csv文件附加到sqlite3数据库中的现有表中。我编写的代码将单个csv文件组合成一个pandas数据框,然后通过添加/组合/删除列来清理该数据框,使其与sqlite数据框中的列相匹配。然后将新的数据帧导出为csv文件。我已经成功地将这个新的csv文件添加到现有数据库中,在数据库中创建了一个新表。我要做的是将新表中的数据添加到数据库中的现有表中,因此我尝试使用UNION语句,但它返回以下错误“ValueError:parameters is a unsupported type”。我知道,当我查看我在数据库中创建的新表时,有些列的类型是“REAL”而不是text(尽管在导出csv之前将它们全部转换为“str”),而表中我想使用UNION连接在一起的所有列都是“text”类型,所以我怀疑是这个问题还是UNION语句本身,但是我我不知道是哪个,也不知道如何修复。非常感谢任何帮助!!在
导入sqlite3 导入操作系统 将熊猫作为pd导入 将numpy作为np导入
def add_CO2_files_到_数据库(files=None):
# If a list of filepaths isn't specified, use every csv file in the same
# directory as the script
if files is None:
# Get the current directory
cwd = os.getcwd()
#Add every csv file that starts with 'FD_' to a list
files = [cwd+'\\'+f for f in os.listdir(cwd) if f.startswith('FD_')]
#Merge the csv files above into single pandas data frame and add a column
for file in files:
df = pd.concat([pd.read_csv(fp).assign(file_name=os.path.basename(fp).split('.')[0]) for fp in files])
#Create a new column called 'FD_serial' from the 'file_name' column
#that consists of just the FD serial number
df['FD_serial'] = df['file_name'].str[0:11]
#Create another column that combines the 'Day', 'Month', and 'Year'
#columns into 1 column called 'date'
df['date'] = df['Month'].map(str)+'-'+df['Day'].map(str)+'-'+df['Year'].map(str)
#Combine columns 'date' and 'Time' into a column called 'date_time'
#then convert column to datetime format
df['date_time'] = pd.to_datetime(df['date'] + ' '+ df['Time'])
#Create new column called 'id' that combines the FD serial number
#'FD_serial' and the timestamp 'date_time' so each line of data has a
#unique identifier in the database
df['id'] = df['FD_serial'].map(str) + '_' + df['date'].map(str) + '_' + df['Time'].map(str)
#Add column 'location' and populate with 'NaN'
df['location'] = np.nan
#Delete unneccesary columns: 'Month', 'Day', 'Year', 'Time', 'date', 'file_name'
df = df.drop(["Month", "Day", "Year", "Time", "date", "file_name", "Mode"], axis=1)
#Rename columns to match the SQLite database conventions
df = df.rename({'Flux':'CO2_flux', 'Temperature (C)':'temp', 'CO2 Soil (ppm) ':'soil_CO2', 'CO2 Soil STD (ppm)':'soil_STD',
'CO2 ATM (ppm)':'atm_CO2', 'CO2 ATM STD (ppm)':'atm_std'}, axis='columns')
#Change data types of all columns to 'str' so it matches the data type in the database
df = df.astype(str)
#Save the merged data frame in a csv file called 'FD_CO2_data.csv'
df.to_csv("FD_CO2_data.csv", index=False)
将\u CO2_文件添加到\u数据库()
将新表中的行插入到现有表中应该很容易
cur.execute("INSERT into CO2 select * from FD_CO2")
这假设FD_CO2中的列直接映射到CO2中的列,并且不会出现插入冲突,例如重复键。您将需要一个
cur.commit()
将行提交到数据库^{} in sqlite 是一个复合查询,它本质上与数学中的并集相同;它返回两个“集”的并集,即select。在
错误
"ValueError: parameters are of unsupported type"
是因为execute
的conn
参数。当sql语句参数化时,execute接受参数。Here's the python doc on the subject.相关问题 更多 >
编程相关推荐