Stack Overflow
Stack Overflow

Questions tagged [pandas-to-sql]

Pandas DataFrame method that writes the object's records to a SQL database. Be sure to also include the [pandas] tag.

Pandas DataFrame method to_sql can be used to write its records to a SQL database.

The documentation:

144 questions
2
votes
1 answer

Using pandas to_sql to append data frame to an existing table in sql server gives IntegrityError

I tried to append my pandas dataframe to an existing data table in sql server like below. All my column names in the data are absolutely identical to the database table.…
zesla
  • 11,155
  • 16
  • 82
  • 147
2
votes
1 answer

psycopg2.ProgrammingError: incomplete placeholder: '%(' without ')'

I have a few different functions that scrape different tables with pandas, saves each to a dataframe, and saves them to a PostgreSQL database. I am able to successfully scrape and save each table as a dataframe, but I am having a bit of an issue…
messy748
  • 327
  • 4
  • 16
2
votes
1 answer

Pandas to_sql: float binary issue

I have a Pandas DataFrame that I'm sending to MySQL via to_sql with sqlalchemy. My floats in SQL sometimes show decimal places that are slightly off (compared to the df) and result in an error: "Warning: (1265, "Data truncated for column 'Dividend'…
drummerof13
  • 43
  • 1
  • 7
2
votes
0 answers

Error while saving a dataframe to Redshift using Python

I'm trying to copy a table from the Redshift database to a dataframe in Python and then save it again in Redshift. So, the first step is working but I have some problems with the second step. I get some errors when I'm trying to save a dataframe…
Katarzyna Malina
  • 21
  • 1
2
votes
0 answers

Cannot change VARCHAR length using pandas and sqlalchemy

I'm using pandas to dynamically insert datasets into a Redshift database. But as I try to change the length of the VARCHAR field, it doesn't seems to work. import pandas as pd import sqlalchemy engine =…
henriqueberlesi
  • 21
  • 3
2
votes
1 answer

Pandas sqlite3: DataFrame.to_sql. "Error binding parameter 0 - probably unsupported type"

I am learning to use pandas and sqlite together. The idea is to read a pandas DataFrame, modify it, and create a sqlite database. this is my code : import sqlite3 # create my database conn = sqlite3.connect('my_db.db') cursor = conn.cursor() #…
Gabriel Durán
  • 31
  • 6
2
votes
1 answer

Appending to a sql table using python panda module

I am attempting to append to a SQL table using to_sql command in python. Everything works fine but if I run my command more tahn once it will obviously append the same data. I know this could be solved with if_exists='replace' but when i do that i…
michaelg
  • 243
  • 2
  • 8
  • 25
1
vote
1 answer

What does a negative return value mean when calling to_sql() in Pandas?

I'm sending various data frames to Microsoft SQL Server using the Pandas function to_sql() and a mssql+pyodbc:// connection made with sqlalchemy.create_engine. Sometimes to_sql() returns the number of rows written, which is what I expect from the…
Sam Firke
  • 21,571
  • 9
  • 87
  • 105
1
vote
0 answers

What is the fastest way to copy Pandas dataframe to PostgreSQL database taking into account datatypes?

I found (and augmented to deal with datatypes) this very good code to copy dataframes to postgreSQL engine = create_engine('postgresql+psycopg2://username:password@host:port/database') # custom function to execute SQL queries read_sql(query): …
Vito Anania
  • 11
  • 1
1
vote
1 answer

Insert or update if primary key exists into postgreSQL table with .to_sql()

I have a pandas DataFrame that consists of multiple columns that I want to store into the postgreSQL database, using .to_sql(): my_table.to_sql('table', con=engine, schema='wrhouse', if_exists='append', index=False) I have set a primary key (date),…
Pythoneer
  • 143
  • 8
1
vote
0 answers

Pyhton - Dataframe to_sql gives sqlalchemy.exc.ProgrammingError

I have a code that reads metadata and it puts the information into a dataframe. That part works, I then use the to_sql function and I get this error: sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('Unknown object type: IFDRational',…
PyMan
  • 132
  • 13
1
vote
0 answers

pandas to_sql not handling nan

I'm just trying to send to sybase ASE some float data containing nan, the expected result being a NULL in the database. The pandas version is 1.3.5, and python Python 3.8.12, and sqlalchemy 1.4.28 . Creation of the table: create table test(head…
Pierre
  • 1,046
  • 7
  • 21
1
vote
1 answer

Is there any way to improve to_sql performance in Python?

I have a loop that goes through each file (xlsx and csv) on a directory, reads them, append them and concatenate it in list. That list is them sent to a database. It works, but hen it comes to bigger files, it can take a lot of time. I figure this…
Catarina Ribeiro
  • 562
  • 2
  • 16
1
vote
1 answer

(dataframe.to_sql with reference_or_insert): How to automatically insert a missing record in a referenced table when a foreign key is not found?

Description I am trying to migrate data from a Pandas DataFrame to a MySQL database table but that data has some inconsistencies that I want to work around though I have not yet figured out a way to. Any help in figuring this out will be very much…
Sam S
  • 168
  • 2
  • 8
1
vote
0 answers

How to set primary key of the Sqlite3 table when using to_sql?

I have DataFrame that is needed to save as a table. I used this one and it works well. result.to_sql( 'tbl_name', index_label='date', dtype={'date': Date}, con=engine.connect(), if_exists='append', index = True ) The difficult…
Chuck
  • 776
  • 5
  • 18
1 2
3
…
9 10