我正在从各种来源（csv，xls，json等）加载数据到Pandas数据帧中，并且希望生成用这些数据创建和填充SQL数据库的语句。有人知道如何做到这一点吗？
我知道Pandas有一个to_sql函数，但它只适用于数据库连接，无法生成字符串。
我想要的是像下面这样的数据框：
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'))
而一个可以生成这个的函数（此示例为PostgreSQL，但任何函数都可以）：
CREATE TABLE data
(
index timestamp with time zone,
"A" double precision,
"B" double precision,
"C" double precision,
"D" double precision
)
如果您只需要 'CREATE TABLE' 的 SQL 代码（而不是数据的插入），则可以使用 pandas.io.sql 模块的 get_schema 函数：
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
)
一些备注：
reset_index，否则它不包括索引。SOURCE = df
TARGET = data
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
CREATE TABLE 语句字符串print('\n\n'.join(sql_text))
def SQL_INSERT_STATEMENT_FROM_DATAFRAME(SOURCE, TARGET):
sql_texts = []
for index, row in SOURCE.iterrows():
sql_texts.append('INSERT INTO '+TARGET+' ('+ str(', '.join(SOURCE.columns))+ ') VALUES '+ str(tuple(row.values)))
return sql_texts
INSERT INTO 语句字符串print('\n\n'.join(sql_texts))
不确定这是否是最佳方法，但这比使用 df.iterrows() 更高效，因为后者非常慢。此外，此方法利用正则表达式处理了nan值。
import re
def get_insert_query_from_df(df, dest_table):
insert = """
INSERT INTO `{dest_table}` (
""".format(dest_table=dest_table)
columns_string = str(list(df.columns))[1:-1]
columns_string = re.sub(r' ', '\n ', columns_string)
columns_string = re.sub(r'\'', '', columns_string)
values_string = ''
for row in df.itertuples(index=False,name=None):
values_string += re.sub(r'nan', 'null', str(row))
values_string += ',\n'
return insert + columns_string + ')\n VALUES\n' + values_string[:-2] + ';'
re 应该是什么？ - Rainbre 代表"正则表达式"。您可以使用命令import re导入Python的正则表达式库。 - eitanleespandas.DataFrame 生成一个带插入的字符串，我建议使用 @rup 建议的批量 SQL 插入语法。
以下是我为此编写的示例 函数：
import pandas as pd
import re
def df_to_sql_bulk_insert(df: pd.DataFrame, table: str, **kwargs) -> str:
"""Converts DataFrame to bulk INSERT sql query
>>> data = [(1, "_suffixnan", 1), (2, "Noneprefix", 0), (3, "fooNULLbar", 1, 2.34)]
>>> df = pd.DataFrame(data, columns=["id", "name", "is_deleted", "balance"])
>>> df
id name is_deleted balance
0 1 _suffixnan 1 NaN
1 2 Noneprefix 0 NaN
2 3 fooNULLbar 1 2.34
>>> query = df_to_sql_bulk_insert(df, "users", status="APPROVED", address=None)
>>> print(query)
INSERT INTO users (id, name, is_deleted, balance, status, address)
VALUES (1, '_suffixnan', 1, NULL, 'APPROVED', NULL),
(2, 'Noneprefix', 0, NULL, 'APPROVED', NULL),
(3, 'fooNULLbar', 1, 2.34, 'APPROVED', NULL);
"""
df = df.copy().assign(**kwargs)
columns = ", ".join(df.columns)
tuples = map(str, df.itertuples(index=False, name=None))
values = re.sub(r"(?<=\W)(nan|None)(?=\W)", "NULL", (",\n" + " " * 7).join(tuples))
return f"INSERT INTO {table} ({columns})\nVALUES {values};"
顺便提一下，它将nan/None条目转换为NULL，并且可以通过关键字参数传递常量列=值对（请参见文档字符串示例中的status="APPROVED"和address=None参数）。
通常，它的工作速度更快，因为任何数据库都会为单个插入执行大量的工作：检查约束、构建索引、刷新、写入日志等。当进行多个操作时，可以通过数据库优化这些复杂的操作，而不是逐个调用引擎。
单条插入查询解决方案
我发现上面的答案都不能满足我的需求。我想创建一个单个的插入语句，将数据框的每一行作为值插入数据库。可以通过以下方式实现：
import re
import pandas as pd
table = 'your_table_name_here'
# You can read from CSV file here... just using read_sql_query as an example
df = pd.read_sql_query(f'select * from {table}', con=db_connection)
cols = ', '.join(df.columns.to_list())
vals = []
for index, r in df.iterrows():
row = []
for x in r:
row.append(f"'{str(x)}'")
row_str = ', '.join(row)
vals.append(row_str)
f_values = []
for v in vals:
f_values.append(f'({v})')
# Handle inputting NULL values
f_values = ', '.join(f_values)
f_values = re.sub(r"('None')", "NULL", f_values)
sql = f"insert into {table} ({cols}) values {f_values};"
print(sql)
db.dispose()
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()
map()包装在list()中，这就像魔法一样解决了问题。感谢你的脚本。 - Doan Vufrom datetime import datetime
import pandas as pd
import seaborn as sns
from sqlalchemy import create_engine
# Load Dataset
dataset_name = 'iris'
df = sns.load_dataset(dataset_name)
# Add Name to Index
df.index.name = 'Id'
# Results
df.head()
# Create Engine with SQL Alchemy (used by pandas)
engine = create_engine(f'sqlite://', echo=False)
# Send data to temporary SQLite3
df.to_sql(name=dataset_name, index=True, con=engine, if_exists='replace')
# Output file
output_file = f'sql - {dataset_name}.sql'
# Para cada
with open(output_file, 'w') as f:
# Date
data_agora = datetime.today().strftime('%Y.%m.%d %H:%M:%S')
f.write(
'/****** Query para criação e inserção de registros no DB ******/\n'
)
f.write('/*\n')
f.write(f'São {len(df)} registros\n')
f.write(f'Obtidos na tabela "{dataset_name}"\n')
f.write('\n')
f.write(f'Query feita por Michel Metran em {(data_agora)},\n')
f.write('*/\n')
f.write('\r\n')
with engine.connect() as conn:
for line in conn.connection.iterdump():
f.write(f'{line}\n')
print(line)
# Close Connection
conn.close()
#!pip3 install traquitanas --upgrade
from traquitanas.data import convert_to_sql
convert_to_sql.convert_dataframe_to_sql(df, output_file, dataset_name)
根据您是否可以放弃生成SQL语句的中间表示，您也可以直接执行插入语句。
con.executemany("INSERT OR REPLACE INTO data (A, B, C, D) VALUES (?, ?, ?, ?, ?)", list(df_.values))
这个方法效果稍微好一些，因为字符串生成的操作较少。
import sqlite3
import pandas as pd
db = './database.db'
csv = './data.csv'
table_name = 'data'
# create db and setup schema
df = pd.read_csv(csv)
create_table_sql = pd.io.sql.get_schema(df.reset_index(), table_name)
conn = sqlite3.connect(db)
c = conn.cursor()
c.execute(create_table_sql)
conn.commit()
# now we can insert data
def insert_data(row, c):
values = str(row.name)+','+','.join([str('"'+str(v)+'"') for v in row])
sql_insert=f"INSERT INTO {table_name} VALUES ({values})"
try:
c.execute(sql_insert)
except Exception as e:
print(f"SQL:{sql_insert} \n failed with Error:{e}")
# use apply to loop over dataframe and call insert_data on each row
df.apply(lambda row: insert_data(row, c), axis=1)
# finally commit all those inserts into the database
conn.commit()