将数据从 Excel 文件插入 SQL Server [英] Inserting data into a SQL server from an excel file

问题描述

首先，抱歉我对数据库缺乏了解，这是我第一次与他们合作.

First of all, sorry for my lack of knowledge regarding databases, this is my first time working with them.

我在尝试从 excel 文件中获取数据并将其放入数据库时​​遇到了一些问题.

I am having some issues trying to get the data from an excel file and putting it into a data base.

使用网站上的答案，我设法通过这样做连接到数据库.

Using answers from the site, I managed to kind of connect to the database by doing this.

import pandas as pd
import pyodbc

server = 'XXXXX'
db = 'XXXXXdb'

# create Connection and Cursor objects
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';Trusted_Connection=yes')
cursor = conn.cursor()

# read data from excel
data = pd.read_excel('data.csv')

但我现在真的不知道该怎么办.

But I dont really know what to do now.

我有 3 个表，它们通过productID"连接，我的 excel 文件模仿数据库，这意味着 excel 文件中的所有列在数据库中都有一个位置.

I have 3 tables, which are connected by a 'productID', my excel file mimics the data base, meaning that all the columns in the excel file have a place to go in the DB.

我的计划是读取 excel 文件并为每列制作列表，然后将每列值插入到数据库中，但我不知道如何创建可以执行此操作的查询.

My plan was to read the excel file and make lists with each column, then insert into the DB each column value but I have no idea how to create a query that can do this.

一旦我得到查询，我认为数据插入可以这样完成:

Once I get the query I think the data insertion can be done like this:

query = "xxxxxxxxxxxxxx"

for row in data:
    #The following is not the real code
    productID = productID
    name = name
    url = url

    values = (productID, name, url)

    cursor.execute(query,values)

conn.commit()
conn.close

数据库看起来像这样.

https://prnt.sc/n2d2fm

http://prntscr.com/n2d3sh

http://prntscr.com/n2d3yj

尝试做这样的事情，但我得到在字符串格式化期间并非所有参数都被转换"类型错误.

Tried doing something like this, but i'm getting 'not all arguments converted during string formatting' Type error.

import pymysql
import pandas as pd

connStr = pymysql.connect(host = 'xx.xxx.xx.xx', port = xxxx, user = 'xxxx', password = 'xxxxxxxxxxx') 

df = pd.read_csv('GenericProducts.csv')

cursor = connStr.cursor()

query = "INSERT INTO [Productos]([ItemID],[Nombre])) values (?,?)"

for index,row in df.iterrows():

    #cursor.execute("INSERT INTO dbo.Productos([ItemID],[Nombre])) values (?,?,?)", row['codigoEspecificoProducto'], row['nombreProducto'])
    codigoEspecificoProducto = row['codigoEspecificoProducto']
    nombreProducto = row['nombreProducto']

    values = (codigoEspecificoProducto,nombreProducto)

    cursor.execute(query,values)


connStr.commit()
cursor.close()
connStr.close() 

我认为我的问题在于我如何定义查询，这肯定不是正确的方法

I think my problem is in how I'm defining the query, surely thats not the right way

推荐答案

试试这个，你好像把库从pyodbc改成mysql了，好像要%s而不是?

Try this, you seem to have changed the library from pyodbc to mysql, it seems to expect %s instead of ?

import pymysql
import pandas as pd

connStr = pymysql.connect(host = 'xx.xxx.xx.xx', port = xxxx, user = 'xxxx', password = 'xxxxxxxxxxx') 

df = pd.read_csv('GenericProducts.csv')

cursor = connStr.cursor()

query = "INSERT INTO [Productos]([ItemID],[Nombre]) values (%s,%s)"

for index,row in df.iterrows():

    #cursor.execute("INSERT INTO dbo.Productos([ItemID],[Nombre]) values (%s,%s)", row['codigoEspecificoProducto'], row['nombreProducto'])
    codigoEspecificoProducto = row['codigoEspecificoProducto']
    nombreProducto = row['nombreProducto']

    values = (codigoEspecificoProducto,nombreProducto)

    cursor.execute(query,values)


connStr.commit()
cursor.close()
connStr.close() 

这篇关于将数据从 Excel 文件插入 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！