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
4
votes
1 answer
Pandas :Record count inserted by Python TO_SQL funtion
I am using Python to_sql function to insert data in a database table from Pandas dataframe.
I am able to insert data in database table but I want to know in my code how many records are inserted .
How to know record count of inserts ( i do not…
PythonDeveloper
- 289
- 1
- 4
- 24
4
votes
2 answers
pandas to_sql for MS SQL
I'm trying to save a dataframe to MS SQL that uses Windows authentication. I've tried using engine, engine.connect(), engine.raw_connection() and they all throw up errors:
'Engine' object has no attribute 'cursor', 'Connection' object has no…
J.D. Marlin
- 253
- 1
- 3
- 15
4
votes
1 answer
Write Pandas Dataframe to MYSQL database with SSH
The Problem
I would like to use the pandas to_sql to write a dataframe to a MYSQL table. However, my connection requires SSH.
What I have tried
I have a successful connection to execute queries with pymysql, but being able to directly use a…
Paroofkey
- 199
- 1
- 11
4
votes
1 answer
pandas.DataFrame.to_sql inserts data, but doesn't commit the transaction
I have a pandas dataframe I'm trying to insert into MS SQL EXPRESS as per below:
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine("mssql+pyodbc://user:password@testodbc")
connection = engine.connect()
data = {'Host':…
Bartek Malysz
- 922
- 5
- 14
- 37
4
votes
1 answer
Pandas 0.20.2 to_sql() using MySQL
I'm trying to write a dataframe to a MySQL table but am getting a (111 Connection refused) error.
I followed the accepted answer here:
Writing to MySQL database with pandas using SQLAlchemy, to_sql
Answer's code:
import pandas as pd
import…
elPastor
- 8,435
- 11
- 53
- 81
3
votes
3 answers
CSV to SQL Server: bulk import nightmare (T-SQL and/or Pandas)
I am trying to bulk insert a .CSV file into SQL Server without much success.
A bit of background:
1. I needed to insert 16 million records into a SQL Server (2017) DB. Each record has 130 columns. I have a field in the .CSV resulting from an API…
Wilmar
- 558
- 1
- 5
- 16
3
votes
2 answers
pandas to_sql in django: insert foreign key into DB
Is there a way to insert foreign keys when using pandas to_sql function?
I am processing uploaded Consultations (n=40k) with pandas in django, before adding them to the database (postgres). I got this working row by row, but that takes 15 to 20…
Niels Hameleers
- 1,201
- 10
- 11
3
votes
0 answers
Python.Dataframe to MySql: MySQL server has gone away
I am trying to write several dataframes in mysql. I use mysql.connector for the connection and sqlalchemy to create an engine.
Most dataframes are written correctly to the database. Unfortunately the application stops with the following…
Kleinstein11
- 95
- 2
- 9
3
votes
0 answers
Invalid argument(s) 'fast_executemany' when using to_sql()
I'm trying to speed up to_sql() by using fast_executemany. But I'm getting an error,
TypeError: Invalid argument(s) 'fast_executemany' sent to create_engine(), using configuration PGDialect_psycopg2/QueuePool/Engine. Please check that the keyword…
Tim
- 80
- 1
- 6
3
votes
1 answer
Pandas leaving idle Postgres connections open after to_sql?
I am doing a lot of ETL with Pandas and Postgres. I have a ton of idle connections, many marked with COMMIT and ROLLBACK, that I am not sure how to prevent from sitting as idle for long periods rather than closing. The main code I use to write to…
Scott Skiles
- 3,647
- 6
- 40
- 64
3
votes
1 answer
pyodbc to sqlalchemy connection
I am trying to switch a pyodbc connection to sqlalchemy.
The working pyodbc connection is:
import pyodbc
con = 'DRIVER={ODBC Driver 11 for SQL Server};SERVER=server.com\pro;DATABASE=DBase;Trusted_Connection=yes'
cnxn = pyodbc.connect(con)
cursor =…
Rafa.Rugamas
- 87
- 1
- 3
- 9
3
votes
4 answers
Connecting to Teradata using Python
I am trying to connect to teradata server and load a dataframe into a table using python. Here is my code -
import sqlalchemy
engine = sqlalchemy.create_engine("teradata://username:passwor@hostname:port/")
f3.to_sql(con=engine, name='sample',…
Ishwor Bhatta
- 139
- 1
- 3
- 13
2
votes
2 answers
How to use to_sql in pandas
I'm trying to get to the bottom of what I thought would be a simple problem: exporting a dataframe in Pandas into a mysql database.
There is a scraper that collates data in pandas to save the csv format
**title, summary, url** #header
abc, …
elksie5000
- 7,084
- 12
- 57
- 87
2
votes
1 answer
Python and Snowflake appending new data into an existing table using SQL Alchemy engine is returning current session does not have a current database
I need to append some new data into an existing table on snowflake. I am using sqlalchemy as the engine along with pandas data frame to_sql(). Here is the imports and the script:
import pandas as pd
import os
import snowflake.connector as…
alim1990
- 4,656
- 12
- 67
- 130
2
votes
2 answers
Python and Snowflake error on appending into existing table on the cloud
I am trying to upload a dataframe into an existing table in snowflake cloud. Here is the dataframe:
columns_df.head()
Now when using the to_sql() from pandas to append data into existing table:
columns_df.to_sql('survey_metadata_column_names',…
alim1990
- 4,656
- 12
- 67
- 130