0

Using Pandas read_sql() function to INSERT data into a SQL table called table_1. Pulling data from a primary database and writing to table_1.

# Creates the table

    read_sql(
        f"""
        CREATE TABLE IF NOT EXISTS {table_1} (id varchar, centroid varchar, date int, thresh bigint)

        """
    )

Using a loop to process multiple days and calling function_1.

date_format = "%Y%m%d"
dates_to_compute = pd.date_range(start='2022-09-01', end='2022-09-10', freq='D').strftime(date_format)

for date in dates_to_compute:
         print(f"Executing date {date}")
         query = f"""
              INSERT INTO {table_1}
              {function_1(id, centroid, date, thresh)}
         """
         read_sql(query)

Here is the error statement:

DatabaseError:
Insert query has mismatched column types:
Table: [varchar, varchar, integer, bigint],
Query: [bigint, varchar, double, double, array(varchar(9))]

My question is can I modify the INSERT statement to change the datatypes so that they match those of table_1 that was created earlier.

Larry
  • 45
  • 1
  • 8
  • **IF** the source data is appropriate, you can `cast` it to the correct data type. The double columns may fail casting to integer. Additionally, you have 4 columns in your table, but your error message shows you trying to insert 5. – Andrew Sep 13 '22 at 15:19
  • I will look into `cast`. I noticed that also 4 vs. 5, but working on one issue at a time. Thanks. – Larry Sep 13 '22 at 15:47
  • Typically, you would only use `read_sql()` for select statements. You would use `DataFrame.to_sql()` to insert into a table. Why are you using `read_sql()` to insert to the table? – Nick ODell Sep 13 '22 at 17:59
  • I am new to this particular function. But I am using existing code and having to modify for multiple dates. The best that I can say is that I am reading from another SQL database and not a pandas DF. – Larry Sep 13 '22 at 21:14

0 Answers0