I'm using Django and bcp to insert huge amounts of data into my database.
As of now, I have a dataframe which I convert to a CSV using to_csv() which my BCP command then reads and uploads to DB.
However, for it to upload correctly, I have to have the file in the exact order as columns in DB and as of now I'm doing it manually like this:
data = data[['id', 'data_date', 'data', 'created_at', 'modified_at', 'signal_id']]
I tried using the following command to get the order of columns from the table as I learned from this answer but it didn't seem to return anything. Process just got stuck
bcp_string_get_col_names = "bcp \"DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from {}.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{}'; select @colnames;\" queryout HeadersOnly.csv -S {} -U {} -P {}".format(database, table, host, user, password)
status = subprocess.run(bcp_string_get_col_names, stdout=subprocess.PIPE)
The command which uploads my data to database looks like this:
bcp_string_upload = 'bcp {} in {} -S {} -d {} -U {} -P {} -t "{}" -c -F {} -b 20000'.format(table, file, host, database, user, password, sep, row_start)
status = subprocess.run(bcp_string_upload, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
I would really appreciate if either you could tell me how to either get the columns from the DB or how to upload using maybe the dataframe headers so we dont care about the order of columns.
