CODEWITHSUNDEEP
pythonsql-serverinsertpyodbc
svanzundert
svanzundert

Reputation: 63

update python last record only

I am a newbie python user and for some reason the below update does not work.

What I expect is that it updates using a API pull and when I use the print it displays exactly what I expect, 5 records. These are 2 seperate XML files that are afterwards processed that contain these 5 records total. One XML has 3 rows, the other 2 rows.

char01
char02
char03
char04
char05

The code makes use of an URL which needs to be filled with a kyeID and a vCode. The keyID and the vCode are stored in a table. This all works fine. And when I do the print it prints 5 seperate records.

Now what it really does is that when it updates it to SQL, only the last record gets inserted 5 times. I have no clue how this can happen. When I do an insert however, it works just perfectly fine.

The end result in the database is the last record 5 times:

char05
char05
char05
char05
char05 

import pyodbc
import time
import tempfile
import cPickle
import zlib
import os
from os.path import join, exists
from httplib import HTTPException

import eveapi

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=MyServer;DATABASE=TESTDB;Integrated Security=True')
cursor = cnxn.cursor()

cursor.execute("SELECT keyID, vCode FROM characters_apikey")

for row in cursor.fetchall():
    sql_keyID = row.keyID
    sql_vCode = row.vCode

    api = eveapi.EVEAPIConnection()
    auth = api.auth(keyID=sql_keyID, vCode=sql_vCode)

    result2 = auth.account.Characters()


    for character in result2.characters:
        print (character.name)
        cursor.execute("update characters set characterName = ?, characterID = ?, corporationName =?, corporationID = ?, allianceID = ?, allianceName = ?, factionID = ?, factionName =? ;", (character.name, character.characterID, character.corporationName, character.corporationID, character.allianceID, character.allianceName, character.factionID, character.factionName))
    cursor.commit()

What am I doing wrong here with this update?

Upvotes: 4

Views: 341

Answers (1)

John Hua
John Hua

Reputation: 1456

The call should look something like:

cursor.execute("update characters set characterName = ?, characterID = ?, corporationName =?, corporationID = ?, allianceID = ?, allianceName = ?, factionID = ?, factionName =? where keyID = ? and vCode = ?;", (character.name, character.characterID, character.corporationName, character.corporationID, character.allianceID, character.allianceName, character.factionID, character.factionName, row.keyID, row.vCode))

Just a kind reminder that you missed the where clause.

Upvotes: 4

Related Questions