pandas get to_sql behind-the-scenes query as string

Nicholas Hansen-Feruch 0
Viewed 20

In pandas, I would like to get the SQL command that is run when something like df.to_sql('table', connection, if_exists='append', method='multi') is run in python.

Meaning the output would look like 'INSERT INTO table (...) VALUES (...)'. Is this possible?

pandas sql python
1 Answers

Considering an SQLite database, the equivalent of pandas.DataFrame.to_sql would be this :

import sqlite3 as sql

### --- CREATING THE DATABASE
conn = sql.connect('database_name.db')

### --- CREATING THE TABLE
cur = conn.cursor()
cur.execute('''CREATE TABLE IF NOT EXISTS TABLE_NAME
           (Col1 INTEGER, Col2 INTEGER, Col3 TEXT)''')

### --- INSERTING NEW ROWS THE TABLE
data = [(val1_1, val2_1, val3_1), (val2_1, val2_2, val3_2)]
cur.executemany('INSERT INTO TABLE_NAME(Col1, Col2, Col3) VALUES (?,?,?)', data)

### --- SAVING THE CHANGES
conn.commit()
conn.close()
abokey0
Related