0

I have a dataframe with some column names and data types as following:

column_name                                   data_type 
modifiedDate                                  DateTime
city                                          VARCHAR(50)
state                                         VARCHAR(50)
region                                        VARCHAR(50)

In order to use it with pandas to_sql() I need to send a dictionary using the column names and the datatypes in the following format:

{'modifiedDate': DateTime,
 'city': VARCHAR(60),
 'state': VARCHAR(60),
 'region': VARCHAR(30)}

I have tried to use zip and other methods to convert to a dictionary but it uses single quotes for the values, which causes the to_sql to fail.

col_type = dict(zip(df['column_name'],df['data_type']))

{'modifiedDate': 'DateTime',
 'city': 'VARCHAR(60)',
 'state': 'VARCHAR(60)',
 'region': 'VARCHAR(30)'}

I have also tried to concatenate the values, then use .tolist() and join, but this also fails because it is a string and to_sql expects a dict.

col_list = '{' + ', '.join([str(elem) for elem in values]) + '}'


'{"addressid":Integer, "addressline1":VARCHAR(60), "addressline2":VARCHAR(60), "city":VARCHAR(30), "stateprovince":VARCHAR(50), "countryregion":VARCHAR(50), "postalcode":VARCHAR(15), "modifieddate":DateTime}'

Any help to achieve this would be much appreciated.

noClueAboutWhatIMDoing
  • 17
  • 5
  • Kind of convoluted, but you could take the resulting string from your second approach and pass it to `ast.literal_eval()`. – MattDMo Jan 16 '23 at 18:46
  • What is the dtype of the `data_type` column in your df? If you change it to `object` you could store actual objects there instead of strings... – MattDMo Jan 16 '23 at 18:47
  • Hey @MattDMo - I have tried that too, I get an error saying: ValueError: malformed node or string: – noClueAboutWhatIMDoing Jan 16 '23 at 18:48
  • Are `DateTime` and `VARCHAR` actual objects in the current scope? I think that's what `literal_eval` is choking on. You need to have `from sqlalchemy.types import DateTime, Integer, VARCHAR` at the top of your code. – MattDMo Jan 16 '23 at 18:59
  • Yes - I have added "from sqlalchemy.types import * " at the beginning of my code – noClueAboutWhatIMDoing Jan 16 '23 at 19:11

0 Answers0