CODEWITHSUNDEEP
pythonmysqlsqlweb-scrapingpyodbc
Zayn
Zayn

Reputation: 61

Uploading data from webscraper to SQL server

I am trying to upload web scraped deals to mysql server:

def sendToSQL(deals, n):
    length = len(deals)
    if length < n:
        print("Not enough deals",length,n)
        return
    server = 'zains.database.windows.net'
    database = 'COSC'
    username = 'zainy'
    password = 'pw'
    driver= '{ODBC Driver 17 for SQL Server}'

    mydb = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)
    mycursor = mydb.cursor()
    for i in deals[0:n]:
        mycursor.execute("CREATE TABLE Dealsea(title text, link text, content text, vendor text)")
        mycursor.execute ("""
                INSERT INTO DealSea
                VALUES
                (i.getTitle(),i.getLink(),i.getContent(),i.getVendor())""")

        mydb.commit()

Gives me this error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find either column "i" or the user-defined function or aggregate "i.getTitle", or the name is ambiguous. (4121) (SQLExecDirectW)')

Upvotes: 0

Views: 51

Answers (1)

SAL
SAL

Reputation: 632

You are passing python variable i as a string which is causing the issue. Try following instead:

mycursor.execute ("""
                INSERT INTO DealSea
                VALUES
                ('"""+i.getTitle()+"""','"""+i.getLink()+"""','"""+i.getContent()+"""','"""+i.getVendor()+"""')""")

When you use multi line comment python considers variable i as a string which causes the issue.

Upvotes: 1

Related Questions