How to Insert or Update in SQL using Python

Question

I have a list in python that I'm currently inserting into a SQL table called SocketTest. Basically I'm storing connection logs. I want to just store the last location AP (access point) someone connected to instead of a list of them like I'm doing now. They read and come through the socket in chronological order so the newest list I get in my function is always the most current one for that user. Basically my program reads from a socket, and makes a list with the information separated by commas, then I insert it into my table and while I still have information in the socket it keeps getting repeated. My list in named i. I've been trying to figure this out myself but I'm not to experienced. I would like to look at the PK which is the ID and insert the line if no ID is stored yet and replace or update the stored row if the ID already exists. I was looking into replace into and insert on duplicate key but couldn't get it to work. Any help is appreciated, code is below.

SocketTest is the Table: PK is the ID: Pringle is an example line:

EDIT: I'm importing MYSQLDB as mdb

def ParseArray(l): #parses line in socke
i.append(l.split()[+0] + '')  # Gets Day
i.append(l.split()[+1] + '')  # Gets Month
i.append(l.split()[+3] + '')  # Gets Year
i.append(l.split()[+2] + '')  # Gets Time
i.append(l.split()[-2] + '')  # Gets Device
i.append(l.split()[+9] + '')  # Gets  ID
i.append(l.split()[+18] + '')  # Gets AP
i.append(l.split()[+19] + '')  # Gets AP Group
i.append(l.split()[+16] + '/n')  # Gets MAC
#insert line into db else by primary key (ID)
#update line to db if ID doesn't exist
#pringle = ['Dec', '11', '2018', '15:10:51', 'iPhone', '[jeref2]', 
#    'home', 'hm1', '45.88.34.58)\n']


sql = "INSERT INTO SocketTest (month, day, year, time, device, Id, ap, 
    ApGroup, MacAdd) VALUES ('%s');" % "', '".join(i)
cur.execute(sql)
con.commit()

Edit: Rest of the code

#!/bin/python

import socket
import os, os.path
import MySQLdb as mdb
con = mdb.connect('x', 'x', 'x', 'x');
cur = con.cursor()



#pringle=['Dec', '11', '2018', '15:10:51', 'iPhone', '[josheer]', 'FRD', 'BHJ', '45.33.88.34)\n']














def ParseArray(l): #parses line in socke
i.append(l.split()[+0] + '')  # Gets Day
i.append(l.split()[+1] + '')  # Gets Month
i.append(l.split()[+3] + '')  # Gets Year
i.append(l.split()[+2] + '')  # Gets Time
i.append(l.split()[-2] + '')  # Gets Device
i.append(l.split()[+9] + '')  # Gets  ID
i.append(l.split()[+18] + '')  # Gets AP
i.append(l.split()[+19] + '')  # Gets AP Group
i.append(l.split()[+16] + '/n')  # Gets MAC

try:
    row_to_insert = [val1, val2, val3]
    cur.execute("INSERT INTO SocketTest (month, day, year, time, device,Id, ap, ApGroup, MacAdd) VALUES(%s, %s, %s)",
                   (i)

except: pymysql.IntegrityError:
    cur.execute("""UPDATE SocketTest
    SET column2 = {1}, column3 = {2}
    WHERE column1 = {0}
    """.format(val1, val2, val3)







#sql = "REPLACE INTO SocketTest (month, day, year, time, device,Id, ap, ApGroup, MacAdd) VALUES ('%s');" % "', '".join(
    #i)
#cur.execute(sql)
#con.commit()





con.commit()








print(i)

del i[:]



i = []

if os.path.exists("/home/log/x"):
os.remove("/home/log/x")

sock = socket.socket(x, sx)
sock.bind("/home/log/xt")
infile = sock.makefile('r')




while True:
    l = sock.recv(4096).decode()
    ParseArray(l)

What's your table schema look like? What database are you using? The operation you're talking about is called `UPSERT` and different databases handle it differently. — Ben, Jan 07 '19 at 20:10
On a small scale it probably isn't noticeable but when you do so many `split()` and `append()` it is *really* inefficient. Have a `list` of indices and create `i` based on one `split()` instead, like `ind = [0, 1, 3, 2, -2, 9, 18, 19, 16]; lst = l.split(); i = [lst[x] for x in ind]` — r.ook, Jan 07 '19 at 20:11
Not getting any errors, it's currently working with insert into, I was just looking for ways on how to do what I'm asking and I can't follow the examples. — Tanner A, Jan 07 '19 at 20:18
Not sure what you mean by library but I'm using maria db, importing socket, os, and MYSQLDB as mdb. — Tanner A, Jan 07 '19 at 20:19
I'm not sure I really understand your problem... If what you want is to either insert or update based on whether a record with a same id already exists then you just have to check if the record exists and take appropriate action, which is quite trivial as long as you don't have concurrent writes. — bruno desthuilliers, Jan 07 '19 at 20:27
also - totally unrelated but - the way you're building your query using string formating is both brittle and VERY unsafe (read about sql injection) — bruno desthuilliers, Jan 07 '19 at 20:29

Finrod Felagund · Answer 1 · 2019-01-07T21:11:53.007

0

In case you are using pymysql library, something like this would do the work:

row_to_insert = [val1, val2, val3]

cursor.execute("INSERT INTO my_table_name (column1, column2, column3) VALUES(%s, %s, %s)", 
    row_to_insert)

more info can be found here: How to use variables in SQL statement in Python?

EDIT after poster's comment:

try:
    row_to_insert = [val1, val2, val3]
    cursor.execute("INSERT INTO my_table_name (column1, column2, column3) VALUES(%s, %s, %s)",
                   row_to_insert)

except pymysql.IntegrityError:
    cursor.execute("""UPDATE my_table_name 
    SET column2 = {1}, column3 = {2}
    WHERE column1 = {0}
    """.format(val1, val2, val3)

edited Jan 07 '19 at 21:11

answered Jan 07 '19 at 20:32

Finrod Felagund

1,231
2
14
18

Yes, what I'm using now works. I just need help in how to go about Inserting or updating based on if a row with the PK already exists. – Tanner A Jan 07 '19 at 20:48
I won't be able to mess with it until I get to my server later today but I will let you know, thank you for taking the time to do this. If you have the time to explain what you did or point me to where I could learn how to do this myself so I can learn from this it would be much appreciated! – Tanner A Jan 08 '19 at 18:17
Hey, I got an error, an exception error with the pointer after except. I'm going to put the rest of my code in the original question. – Tanner A Jan 08 '19 at 21:54
This was the error: File "./dbtest.py", line 57 except: pymysql.IntegrityError: I edited the main post to include the entire code. – Tanner A Jan 08 '19 at 22:04
You are using MySQLdb libraly, the solution I provided you with is for pymysql. Eather use pymysql or in your case I suppose you should replace except pymysql.IntegrityError: with except mdb.Error: – Finrod Felagund Jan 08 '19 at 22:20
Tried importing pymysql and I aslo tried changing it to except mdb.Error: and I'm still getting the same error. Any other ideas? – Tanner A Jan 09 '19 at 17:39

score 0 · Answer 2 · answered Jan 07 '19 at 20:52

I'd rewrite your function as:

def ParseArray(l): #parses line in socke
  day = (l.split()[+0] + '')  # Gets Day
  month = (l.split()[+1] + '')  # Gets Month
  year = (l.split()[+3] + '')  # Gets Year
  time = (l.split()[+2] + '')  # Gets Time
  device = (l.split()[-2] + '')  # Gets Device
  Id = (l.split()[+9] + '')  # Gets  ID
  ap = (l.split()[+18] + '')  # Gets AP
  ApGroup = (l.split()[+19] + '')  # Gets AP Group
  MacAdd = (l.split()[+16] + '')  # Gets MAC
  #print (day, month, year, time, device, Id, ap, ApGroup, MacAdd)
  #insert line into db else by primary key (ID)
  #update line to db if ID doesn't exist
  #pringle = ['Dec', '11', '2018', '15:10:51', 'iPhone', '[jeref2]',
  #    'home', 'hm1', '45.88.34.58)\n']

  sql = "INSERT INTO SocketTest (month, day, year, time, device, Id, ap, ApGroup, MacAdd) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);"
  cur.execute(sql, (day, month, year, time, device, Id, ap, ApGroup, MacAdd))
  con.commit()

How to Insert or Update in SQL using Python

2 Answers2