通过Python将文件插入MS SQL Server

Question

我是编程的新手。我已经通过研究stackoverflow和其他站点来编写以下代码。我正在尝试将CSV文件上传到MS SQL Server。每次我运行时，它都会连接，然后弹出消息"以前的SQL不是查询"。我不确定如何真正解决这个问题。任何建议和帮助将不胜感激

import pyodbc import _csv
source_path= r'C:UsersuserDocumentsQA Canvasmodule2Module 2 ChallengeUFO_Merged.csv'
source_expand= open(source_path, 'r')
details= source_expand.readlines
print('Connecting...')
     try:
    conn = pyodbc.connect(r'DRIVER={ODBC Driver 13 for SQL Server};'r'SERVER=FAHIMSQLEXPRESS;'r'DATABASE=Ash;'r'Trusted_Connection=yes')
    print('Connected')
    cur = conn.cursor()
    print('Cursor established')
    sqlquery ="""
                IF EXISTS
                   (
                    SELECT TABLE_NAME ,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_NAME = 'UFO_MERGED' AND TABLE_SCHEMA = 'dbo')
                    BEGIN
                    DROP TABLE [dbo].[UFO_MERGED]
                    END
                    CREATE TABLE [dbo].[UFO_MERGED]
                    (      [ID] smallint
                          ,[COMMENTS] varchar(max)
                          ,[FIRST OCCURANCE] datetime
                          ,[CITY] varchar(60)
                          ,[COUNTRY] varchar(20)
                          ,[SHAPE] varchar(20)
                          ,[SPEED] smallint
                          ,[SECOND OCCURANCE] datetime
                    PRIMARY KEY(id)
                    ) ON [PRIMARY]
                             """
    result = cur.execute(sqlquery).fetchall()
    for row in result:
        print(row)
    print("{} rows returned".format(len(result)))
    sqlstr= """
              Insert into [dbo].[UFO_Merged] values  ('()','()','()','()','()','()','()','()')             
              """
    for row in details[1:]:
        row_data =row.split(',')
        sqlquery=sqlstr.format(row_data[0],row_data[1],row_data[2],row_data[3],row_data[4],row_data[5],row_data[6],row_data[7])
        result=cur.execute(sqlquery)
    conn.commit()    
    conn.close()

except Exception as inst:
    if inst.args[0]== '08001':
        print("Cannot connect to the server")
    elif inst.args[0] == '28000':
        print("Login failed - check connection string")
    else:
        print(inst)

Answer 1

好吧，请确保先确保首先工作，然后再尝试在其顶部引入其他技术(Python，R，C＃等(。SQL看起来有些时髦，但是我不是SQL专家，所以我不能肯定地说，我没有时间在我的机器上重新创建您的设置。也许您可以尝试一些不那么复杂的事情，使工作工作，然后毕业于更先进的事情。以下适合您的工作吗？

import pyodbc
user='sa'
password='PC#1234'
database='climate'
port='1433'
TDS_Version='8.0'
server='192.168.1.103'
driver='FreeTDS'
   con_string='UID=%s;PWD=%s;DATABASE=%s;PORT=%s;TDS=%s;SERVER=%s;driver=%s' % (user,password, database,port,TDS_Version,server,driver)
   cnxn=pyodbc.connect(con_string)
   cursor=cnxn.cursor()
   cursor.execute("INSERT INTO mytable(name,address) VALUES (?,?)",('thavasi','mumbai'))
   cnxn.commit()