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
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…
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…
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
1
2
3
9 10