Frage Generiere SQL-Anweisungen aus einem Pandas-Dataframe


Ich lade Daten aus verschiedenen Quellen (csv, xls, json etc ...) in Pandas-Datenrahmen und möchte Anweisungen erstellen, um eine SQL-Datenbank mit diesen Daten zu erstellen und zu füllen. Kennt jemand einen Weg, dies zu tun?

Ich weiß, Pandas hat eine to_sql Funktion, aber das funktioniert nur für eine Datenbankverbindung, es kann keine Zeichenfolge generieren.

Beispiel

Was ich möchte, ist einen Datenrahmen wie folgt zu nehmen:

import pandas as pd
import numpy as np

dates = pd.date_range('20130101',periods=6)
df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=list('ABCD'))

Und eine Funktion, die dies erzeugen würde (dieses Beispiel ist PostgreSQL, aber alle wären in Ordnung):

CREATE TABLE data
(
  index timestamp with time zone,
  "A" double precision,
  "B" double precision,
  "C" double precision,
  "D" double precision
)

6
2018-06-26 11:22


Ursprung


Antworten:


Wenn Sie nur den SQL-Code 'CREATE TABLE' (und nicht die Einfügung der Daten) haben wollen, können Sie den get_schema Funktion des Moduls pandas.io.sql:

In [10]: print pd.io.sql.get_schema(df.reset_index(), 'data')
CREATE TABLE "data" (
  "index" TIMESTAMP,
  "A" REAL,
  "B" REAL,
  "C" REAL,
  "D" REAL
)

Einige Notizen:

  • Ich musste es benutzen reset_index weil es ansonsten den Index nicht enthielt
  • Wenn Sie eine Sqlalchemy-Engine mit einem bestimmten Datenbank-Flavor bereitstellen, wird das Ergebnis an diesen Flavor angepasst (z. B. die Datentypnamen).

10
2018-06-26 14:23



Wenn Sie die Datei selbst schreiben möchten, können Sie auch Spaltennamen und dtypes abrufen und ein Wörterbuch erstellen, um Pandas-Datentypen in SQL-Datentypen umzuwandeln.

Als Beispiel:

import pandas as pd
import numpy as np

dates = pd.date_range('20130101',periods=6)
df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=list('ABCD'))

tableName = 'table'
columnNames = df.columns.values.tolist()
columnTypes =  map(lambda x: x.name, df.dtypes.values)

# Storing column names and dtypes in a dataframe

tableDef = pd.DataFrame(index = range(len(df.columns) + 1), columns=['cols', 'dtypes'])

tableDef.iloc[0]           = ['index', df.index.dtype.name]
tableDef.loc[1:, 'cols']   = columnNames
tableDef.loc[1:, 'dtypes'] = columnTypes

# Defining a dictionnary to convert dtypes

conversion = {'datetime64[ns]':'timestamp with time zone', 'float64':'double precision'}

# Writing sql in a file

f = open('yourdir\%s.sql' % tableName, 'w')

f.write('CREATE TABLE %s\n' % tableName)
f.write('(\n')

for i, row in tableDef.iterrows():
    sep = ",\n" if i < tableDef.index[-1] else "\n"
    f.write('\t\"%s\" %s%s' % (row['cols'], conversion[row['dtypes']], sep))

f.write(')')

f.close()

Sie können die Tabelle auch mit INSERT INTO füllen.


2
2018-06-26 15:37



GENERATE SQL CREATE STATEMENT VON DATAFRAME

SOURCE = df
TARGET = data

GENERATE SQL CREATE STATEMENT VON DATAFRAME

def SQL_CREATE_STATEMENT_FROM_DATAFRAME(SOURCE, TARGET):

# SQL_CREATE_STATEMENT_FROM_DATAFRAME(SOURCE, TARGET)
# SOURCE: source dataframe
# TARGET: target table to be created in database

    import pandas as pd
    sql_text = pd.io.sql.get_schema(SOURCE.reset_index(), TARGET)   
    return sql_text

Überprüfen Sie die SQL CREATE TABLE Anweisungszeichenfolge

print('\n\n'.join(sql_text))

GENERATE SQL-INSERT-Anweisung von DATAFRAME

def SQL_INSERT_STATEMENT_FROM_DATAFRAME(SOURCE, TARGET):

    sql_texts = []

    for index, row in SOURCE.iterrows():

        sql_text = 'INSERT INTO '+TARGET+' ('+ str(', '.join(SOURCE.columns))+ ') VALUES '+ str(tuple(row.values))


    return sql_texts

Überprüfen Sie die SQL INSERT INTO Anweisungszeichenfolge

print('\n\n'.join(sql_texts))

0
2017-08-01 08:51