如何在 SQL 中将数据框另存为表

本文介绍了如何在 SQL 中将数据框另存为表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个 SQL Server，在该服务器上有我想使用 Pandas 更改数据的数据库.我知道如何使用 pyodbc 将数据获取到 DataFrame 中，但是我不知道如何将该 DataFrame 返回到我的 SQL Server 中.

I have a SQL Server on which I have databases that I want to use pandas to alter that data. I know how to get the data using pyodbc into a DataFrame, but then I have no clue how to get that DataFrame back into my SQL Server.

我尝试使用 sqlalchemy 创建一个引擎并使用 to_sql 命令，但我无法让它工作，因为我的引擎永远无法正确连接到我的数据库.

I have tried to create an engine with sqlalchemy and use the to_sql command, but I can not get that to work because my engine is never able to connect correctly to my database.

import pyodbc import pandas server = "server" db = "db" conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+db+';Trusted_Connection=yes') cursor = conn.cursor() df = cursor.fetchall() data = pandas.DataFrame(df) connmit()

推荐答案

您可以使用 pandas.DataFrame.to_sql 将您的数据框插入 SQL 服务器.此方法支持 SQLAlchemy 支持的数据库.

You can use pandas.DataFrame.to_sql to insert your dataframe into SQL server. Databases supported by SQLAlchemy are supported by this method.

以下是如何实现此目标的示例:

Here is a example how you can achieve this:

from sqlalchemy import create_engine, event from urllib.parse import quote_plus import logging import sys import numpy as np from datetime import datetime, timedelta # setup logging logging.basicConfig(stream=sys.stdout, filemode='a', format='%(asctime)s.%(msecs)3d %(levelname)s:%(name)s: %(message)s', datefmt='%m-%d-%Y %H:%M:%S', level=logging.DEBUG) logger = logging.getLogger(__name__) # get the name of the module def write_to_db(df, database_name, table_name): """ Creates a sqlalchemy engine and write the dataframe to database """ # replacing infinity by nan df = df.replace([np.inf, -np.inf], np.nan) user_name = 'USERNAME' pwd = 'PASSWORD' db_addr = '10.00.000.10' chunk_size = 40 conn = "DRIVER={SQL Server};SERVER="+db_addr+";DATABASE="+database_name+";UID="+user_name+";PWD="+pwd+"" quoted = quote_plus(conn) new_con = 'mssql+pyodbc:///?odbc_connect={}'.format(quoted) # create sqlalchemy engine engine = create_engine(new_con) # Write to DB logger.info("Writing to database ...") st = datetime.now() # start time # WARNING!! -- overwrites the table using if_exists='replace' df.to_sql(table_name, engine, if_exists='replace', index=False, chunksize=chunk_size) logger.info("Database updated...") logger.info("Data written to '{}' databsae into '{}' table ...".format(database_name, table_name)) logger.info("Time taken to write to DB: {}".format((datetime.now()-st).total_seconds()))

调用此方法会将您的数据框写入数据库，请注意，如果数据库中已有同名表，它将替换该表.

Calling this method should write your dataframe to the database, note that it will replace the table if there is already a table in the database with the same name.