0

I am trying to insert rfid tag reads from a socket using Python into a SQL Server database. Since I'm using a tcp socket, the data will sometimes combine and put multiple entries into a single row and i need each read to be a different row. I have combed through multiple sites and the closest thing that i could find was this Python Socket is receiving inconsistent messages from Server but I wasn't able to make it work.

I am able to use len to separate them, but I have no way of possibly knowing how many reads will be combined together. here is my current code (section that is commented out is using len)

while 1: 
def data_entry():
        c.execute('INSERT INTO ORFinishing(Tag) VALUES(?)',(data2))
       
        conn.commit()
    
data = s.recv(1024)

data2= data.decode("utf-8").rstrip('\n')
print(data2)


if len(data2) ==24:

    print("correct")
    
    data_entry()
    del data2
    
    
else:
    print("wrong")
    data3 = str(data2)
    data4 = data3.split('\n')
    print(data4)
    
    
    def many():
        c.executemany('INSERT INTO ORFinishing(Tag) VALUES(?)', data4)
        conn.commit()
        
        many()
    
    # one,two = data2[:24], data2[-24:]
        
    
    # if len(one)==24:
    #     def data_entry1():
    #         c.execute('INSERT INTO ORFinishing(Tag) VALUES(?)',(one))
       
    #         conn.commit()
    
    #     data_entry1()
        
    # if len(two)==24:
    #     def data_entry2():
    #         c.execute('INSERT INTO ORFinishing(Tag) VALUES(?)',(one))
       
    #         conn.commit()
    
    #     data_entry2()

I have seen several instances of people using executemany but it was always manually entering a tuple or list. I also tried turning it into a tuple. here is the output that i get.

runfile('C:/a/tcpsocket.py', wdir='C:/a') 623037353035000000000000 correct 623031323638000000000000 623031323734000000000000 623033323830360000000000 wrong ['623031323638000000000000', '623031323734000000000000', '623033323830360000000000']

but it doesn't insert any of the data from the string split (wrong section) into the database. for the case above, it only had one read in the database. Using len does put it into the database but it could be 5 reads combined or 100 reads combined so its not really a viable option.

edit: I tried another code that i know for a fact separates the data as it comes in. I got it from here - Using delimiters in a Python TCP stream. The problem is that none of it is being inserted into my database. here is the code.

try: acumulator = "" while True:

    #received = str(s.recv(1024), "utf-8")
    received = str(s.recv(1024).decode("utf-8"))
    divided_message = received.split('\n')
    
    if len(divided_message) >= 2:
        #print('line 1: ', acumulator + divided_message[0].strip())
        data1= str(acumulator + divided_message[0].strip())
        print('line 10: ' + data1)
        
        def data_entry():
                  c.executemany('INSERT INTO ORFinishing(Tag) VALUES(?)',(data1))
                  conn.commit()
                  data_entry()
                 
        
        for i in range(1, len(divided_message) - 1):
            #print('line 2: ', divided_message[i].strip()) 
            data2 = str(divided_message[i].strip())
            print("line 5: " + data2)
            
            
            def data_entry2():
                  c.executemany('INSERT INTO ORFinishing(Tag) VALUES(?)',(data2))
                  conn.commit()
                  data_entry2()
               
            
        if '\n' in divided_message[-1]:
            print('line 3: ', divided_message[-1].strip())
            acumulator = ''
            
            
            data3 = str(divided_message[-1].strip())
            def data_entry3():
                  c.executemany('INSERT INTO ORFinishing(Tag) VALUES(?)',(data3))
                  conn.commit()
                  data_entry3()
                  del data3
            
        else:
            acumulator = str(divided_message[-1])
            
            def data_entry4():
                  c.execute('INSERT INTO ORFinishing(Tag) VALUES(?)',(acumulator))
                  conn.commit()
                  data_entry4()
                  del data2
            
    else:
        acumulator += divided_message[0]
        print("else: " + acumulator)
        # data_entry()
        # del data1
justen 0351
  • 1
  • 1

1 Answers1

0

I figured out the answer myself. I had made it way too complicated. here is the full code in case anyone else runs into the same problem.

import socket
import pyodbc

conn = pyodbc.connect('Driver=your_driver;'
                      'Server=your_server;'
                      'Database=your_database;'
                      'Trusted_Connection=yes;')
 
c=conn.cursor()

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect(("your ip address", your socket))

sql = "INSERT INTO your_table(your column) VALUES(?)"

while 1: 
    data = s.recv(1024)
    data2= data.decode("utf-8").rstrip('\n')
    
    if len(data2) ==24:
        c.execute(sql,(data2))
        conn.commit()
        print("correct: " + data2)
        
    else:
        
        data3 = data2.split("\n")
        for split in data3:
            print("fix: " + split)
            c.execute(sql, split)
            conn.commit()
     

s.shutdown(socket.SHUT_RD)
s.close()
justen 0351
  • 1
  • 1