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:
Questions tagged [pandas-to-sql]
144 questions
1
vote
0 answers
How do I proper upload a huge pandas dataframe into a postgres sql database?
I am trying to upload a very big pandas df (13230 rows x 2502 cols) into a postgres database. I am uploading the dataframe with the function df_to_sql but it gives me this error:
tables can have at most 1600 columns
Therefore I split the df into…
arrabattapp man
- 383
- 1
- 3
- 11
1
vote
1 answer
Pandas to_sql to localhost table returns 'Engine' object has no attribute 'cursor'
I see lots of this question is about sqlite, but mine is to MySQL.
my entire script is like this:
df = pd.read_csv("df.csv")
engine = sqlalchemy.create_engine('mysql+mysqlconnector://{0}:{1}@{2}/{3}'.
…
EntzY
- 467
- 4
- 11
1
vote
1 answer
Geopandas to_sql shows geometry column as text in mysql
I am attempting to take multipolygon data from a geojson, read it into a dataframe, and then create a table in mysql containing the data.
What's weird to me is that checking the dtype at the end of the script will show the geometry column correctly…
br0steen
- 21
- 1
- 4
1
vote
0 answers
Load Teradata table from Python Pandas Dataframe
I am getting below error while trying to load Teradata table from Python Pandas, Any idea ?
teradatasql and pandas - writing dataframe into TD table - Error 3707 - Syntax error, expected something like '(' between the 'type' keyword and '='
import…
BalajiR
- 11
- 1
1
vote
1 answer
Problem with inserts data from Python (to_sql) to SQL Server
I have some problems with symbol #.
Some data in columns contains the symbol #, for example
'JRE#150'
'July banner #150'
When I am inserting data from file all.csv into SQL Server, records containing this character are not inserted into the table…
Alex
- 147
- 8
1
vote
1 answer
pandas to_sql can't adapt type 'dict'
I have to write a pandas dataframe to a PostgreSQL table. Here you find a snippet of code:
values = [1, 1]
d = {'col1': values, 'col2': values}
df = pd.DataFrame(data=d)
df.to_sql('prograd_dashboard', con=dbConnection, if_exists='replace',…
spirogyra
- 21
- 2
1
vote
0 answers
Error using method and chunksize in pandas to_sql
I am trying to speed up the pandas .to_sql() function as it currently takes ~30 mins to dump a table of 22 columns and 100K rows to a MS SQL Server Db. I've tried using the method='multi' and chunksize=1000 (I've read is the max for sql server) but…
d789w
- 357
- 5
- 19
1
vote
1 answer
I Get TypeError: cannot use a string pattern on a bytes-like object when using to_sql on dataframe python 3
Hi I am trying to write a dataframe to my sql database using df.to_sql however I am getting the error message: TypeError: cannot use a string pattern on a bytes-like object. I am using Python 3.
I am using a path on my drive which I can unfortuantly…
Boaz
- 21
- 3
1
vote
2 answers
Pandas Dataframe to SQL Server
I have an API service and in this service I'm writing pandas dataframe results to SQL Server.
But when I want to add new values to the table, I cannot add. I've used append option because in the documentation it says that it adds new values to the…
Beyza
- 149
- 1
- 11
1
vote
1 answer
How to insert a pandas dataframe into a table with a column that contains a list
I have a pandas dataframe where one of the columns contains a list format tield (i.e. [2ndchance])
I want to insert it in a database as it is, but I am obtaining an error.
I ame executing the following sentence:
df_playbyplay.to_sql('playbyplay',…
agm
- 317
- 3
- 15
1
vote
0 answers
sqlalchemy.exc.InterfaceError: Failed executing the operation; Python type Point cannot be converted
I'm trying to insert a dataframe which has this format
{'id': 67972, 'year': 2013, 'country_id': 432, 'country': 'Mali', 'best': 31, 'type_of_violence': 1, 'date_start': '2013-01-11', 'geometry': 'POINT (-3.89474 14.94329)', 'ISO3': 'MLI', 'ISO2':…
K.U
- 293
- 1
- 4
- 15
1
vote
1 answer
Make Python wait until to_sql has finished
I'm using to_sql to export my dataframe into a SQL Server table however I want to delay the execution of the next line of python code until the insertion has been completed.
I have a OKish understanding of database connections but I'm a bit of a…
Big_Daz
- 141
- 1
- 7
1
vote
0 answers
Pandas to_sql discarding rows when appending to mysql table
I'm working with articles scraped from online newspapers with a mysql database and python. I want to use pandas to_sql method on a dataframe for appending recently scraped articles to a mysql table. It works pretty well, but im having some problems…
ebravo
- 55
- 1
- 8
1
vote
0 answers
DataError: (pyodbc.DataError) ('String data, right truncation: length 6454 buffer 6452', '22001') [SQL: u'INSERT INTO
With the help of python3, pandas and pyodbc (version 4.0.24), we are tying to upload a data frame with a column "Review" with usually large amounts of text in it. In the middle of the upload process we get the below error:
DataError:…
Héctor Miquel Vidal
- 81
- 1
- 8
1
vote
0 answers
How to solve Pandas Series to sql storage error?
I am trying to import csv files into a pandas Dataframe and write this Dataframe to a PostgreSQL database. However, after the writing of the Dataframe to the SQL Database, rows appear in the final SQL Database that are not in the Dataframe.
The…
JeroenvH
- 11
- 1