转换PandasDataFrame而不连接到SQL数据库

n53p2ov0 于 2022-12-02 发布在其他

关注(0)|答案(1)|浏览(130)

All solutions I have seen require connecting to a SQL database, which IS NOT the goal of this question.

The Goal Is To Convert A DataFrame To A String Capturing How To Re-Create The DataFrame That I Can Save As A Valid .sql File

Let's say I have a simple pandas DataFrame:

df = pd.DataFrame({{'hello'}:[1], {'world}:[2]})

...and I wanted to automatically convert it into a .sql file that could be executed to generate the table, so something like:

#psuedocode

py_script.output_file_sql('my_table')

  return  """CREATE TABLE my_table (

      hello   integer,

      world   integer

);""

Problem:

I can't find the documentation for pandas conversion into an .sql without actually connecting to a database.
If I use sqlalchemy, then run a query with information_schema.columns or \d table_name that doesn't seem to work.
Any suggestions?

pandas

来源：https://stackoverflow.com/questions/74645609/convert-pandas-dataframe-without-connecting-to-a-sql-database

1条答案

按热度按时间

sqxo8psd1#

你需要正确地Map所有的数据类型，我只是用一个例子来告诉你top是如何启动的。
但是，为了正确起见，如果您希望拥有所有选项，则需要重新构建所有https://www.postgresql.org/docs/current/sql-createtable.html
所以我重复我的评论，最好是用备份工具在数据库服务器上备份你的数据库，并使用帽子代替。

import pandas as pd
df = pd.DataFrame({'hello':[1], 'world':[2]})
df.name = 'Ones'
indextext = "hello"
def typeconversion(x):
    return {
        'int64': 'bigint ',
        'float64': 'FLOAT'
    }[x]

def get_sql(df,Indexx_table):

    STR_sql = "CREATE TABLE " +  df.name + "( "

    for (col1, col2) in zip(df.columns, df.dtypes):    
        STR_sql += col1 + " " + typeconversion(col2.name) + ','
    #remove last comma
    STR_sql = STR_sql[:-1]
    if Indexx_table:
        STR_sql += ", PRIMARY KEY (" + Indexx_table + ")"
    STR_sql += ")"
    return STR_sql

print(get_sql(df,indextext))

结果是

CREATE TABLE Ones( hello bigint ,world bigint , PRIMARY KEY (hello))

赞(0）回复(0）举报 2022-12-02

我来回答

转换PandasDataFrame而不连接到SQL数据库

The Goal Is To Convert A DataFrame To A String Capturing How To Re-Create The DataFrame That I Can Save As A Valid .sql File

Problem:

1条答案

相关问题

热门标签

最新问答