如何将数据从dataFrame加载到mssql表中

engine = sqlalchemy.create_engine('mssql+pyodbc://<Username>:<password>@<DBServername>/<DBName>', pool_pre_ping=True)

I have data to be loaded into a DataFrame, by using connection string when i loop through each row in DF i am able to insert data into the table(1 row at a time using an INSERT INTO.. statement) but i want to use <DataFrame>.to_sql("<Table_name>", engine, if_exists='append')

我有数据要加载到DataFrame,通过使用连接字符串,当我遍历DF中的每一行时,我能够将数据插入表中(使用INSERT INTO ..语句一次1行)但我想使用 .to_sql(“ ”,engine,if_exists ='append') taframe>

Please help me understand if i am missing anything?

请帮我理解我是否遗漏了什么?

but i keep getting this error

但我一直收到这个错误

/Library/Python/2.7/site-packages/sqlalchemy/connectors/pyodbc.py:79: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
  "No driver name specified; "
Traceback (most recent call last):
  File "/Users/snaidu/renuka/python-projects/staging/test.py", line 131, in <module>
    print(loaddmvcsvfile())
  File "/Users/snaidu/renuka/python-projects/staging/test.py", line 115, in loaddmvcsvfile
    result.to_sql("table1", engine, if_exists='append')
  File "/Library/Python/2.7/site-packages/pandas/core/generic.py", line 1534, in to_sql
    chunksize=chunksize, dtype=dtype)

1 个解决方案

#1

You need to specify the SQL driver which you want to use, for MS-SQL, this will be the native driver SQL Server Native Client 11.0

您需要指定要使用的SQL驱动程序,对于MS-SQL,这将是本机驱动程序SQL Server Native Client 11.0

So for example you could use something like this:

例如,您可以使用以下内容:

cxn_string = 'mssql+pyodbc://<Username>:<password>@<DBServername>/<DBName>?driver=SQL+Server+Native+Client+11.0'
engine = sqlalchemy.create_engine(cxn_string, pool_pre_ping=True)

But that is a little long, and hard to read, so I typically make a helper function, something like this

但这有点长,难以阅读,所以我通常会做一个辅助函数,就像这样

import sqlalchemy
import urllib

def sql_connect(server, username, password, database, **kwargs):
'''Connect to SQL server'''
parameters = {'driver': 'SQL Server Native Client 11.0'}

string = r'mssql+pyodbc://{username}:{password}@{server}/{database}?{parameters}'
cxn_string = string.format(server=server,
                           username=username,
                           password=password,
                           database=database,
                           parameters=urllib.parse.urlencode(parameters))
return sqlalchemy.create_engine(cxn_string, **kwargs)

#1