如何通过使用python将Excel数据放入SQL Microsoft服务器数据库中

I'm stuck here, I wonder how I can transfer excel files with data to my database through python? Appreciate answers.

我被困在这里,我想知道如何通过python将excel文件与数据传输到我的数据库?欣赏答案。

My local host is called DB_01<br>

我的本地主机名为DB_01

My database is called dbo.test1

我的数据库名为dbo.test1

My tables contain:

我的表包含:

a - int
b - int
c - int
d - int
e - int
f - int

Would it be okay to use Panda's here?

可以在这里使用Panda吗?

2 个解决方案

#1

This is possible. It is hard to answer your question with such little information, but I suggest you to look at using the dataframe to_sql method of pandas. This also requires creating a connection to your database with sqlalchemy.

这个有可能。用这么少的信息很难回答你的问题,但我建议你看看使用pandas的数据帧to_sql方法。这还需要使用sqlalchemy创建与数据库的连接。

Something along the lines of:

有点像:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname', echo=False)

df = pd.read_csv("your_file.csv")

df.to_sql('your_table_name', con=engine)

The above is just pseudocode. Check out the documentation on setting up a sqlalchemy connection here.

以上只是伪代码。在此处查看有关设置sqlalchemy连接的文档。

#2

Thank you wfbbrown :)

谢谢wfbbrown :)

Will the code by the same if my files is xlsx(shift csv to xlsx)?

如果我的文件是xlsx(将csv转换为xlsx),代码是否会相同?

I hope this can explain more( I use SQL Microsoft server)

我希望这可以解释更多(我使用SQL Microsoft服务器)

My database look like this: ID Block BlockSell BlockBuy RejectedBlock Netimports

我的数据库看起来像这样:ID Block BlockSell BlockBuy RejectedBlock Netimports

And I want have data under each elementes.

我希望每个元素下都有数据。

#1