Python 3 MySQL database insert error - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Python 3 MySQL database insert error (/thread-4234.html) |
Python 3 MySQL database insert error - georgian2all - Aug-01-2017 Hello all and a good day. I have a little problem with this Python script and any help will be appreciated. Thank you in advance for your support. I'm a beginner to Python programming (no more than 6 months of learning and practice). I have a gps tracker atached to my car and another to my father car. I want to log al data sent by this two GPS tracker to a database for future statistics. Everything works fine till I try to write data received over TCP into mysql table. I tried to find a solution to my problem but I did not succeed. Any ideea, help, no matter how small I would be will be apreciated. Terminal error message Quote: Get data from ('tracker_ip', 25904) ('tracker_id', latitude, longitude, '102456', '010817', 'N', 'E', '0', '0.00', 10.3) error: uncaptured python exception, closing channel <main.EchoHandler connected tracker_ip:25904 at 0x7f627f6e2b38> (:dbinsert() missing 1 required positional argument: 'data_tuple' [/usr/lib/python3.5/asyncore.py|read|83] [/usr/lib/python3.5/asynco re.py|handle_read_event|423] [server3_1.py|handle_read|84]) #!/usr/bin/python3 import pymysql import asyncore import socket import re class Database(): def __init__(self): self.dbconnection = pymysql.connect('localhost','db_user','password','db' ) self.cursor = self.dbconnection.cursor() sql = """CREATE TABLE IF NOT EXISTS GPS ( signature CHAR(50) NOT NULL, latitude REAL, orientationNS CHAR(1), longitude REAL, orientationEV CHAR(1), gpstime CHAR(10), gpsdate CHAR(10), speed CHAR(6), direction CHAR(3), distance REAL)""" self.cursor.execute(sql) self.dbconnection.commit() def dbinsert(self,data_tuple): cmd =("""INSERT INTO GPS (signature,latitude,longitude,gpstime,gpsdata,orientationNS,orientationEV,direction,speed,distance)\ VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""") self.cursor.execute(cmd,data_tuple) self.dbconnection.commit() def __del__(self): self.dbconnection.close() class ClientHandler(asyncore.dispatcher): def ConvertCoordinates(self,latitude,longitude): lat_degree = int(float(latitude) / 100); lng_degree = int(float(longitude) / 100); # Extracting minutes for latitude and longitude lat_mm_mmmm = float(latitude) % 100 lng_mm_mmmmm = float(longitude) % 100 converted_latitude = lat_degree + (lat_mm_mmmm / 60) converted_longitude = lng_degree + (lng_mm_mmmmm / 60) return (converted_latitude,converted_longitude) #tuple def GetData(self,data_string): data_list = re.sub(r'\s', '', data_string).split(',') signature = data_list[1] latitude,longitude = self.ConvertCoordinates(data_list[5],data_list[7]) orientationNS = data_list[6] #latitude orientationEV = data_list[8] #longitude gpstime = data_list[3] gpsdate =data_list[11] gps_fix_validate = data_list[4] # speed= data_list[9] direction = data_list[10] distance = 10.3 #dummy data return (signature,latitude,longitude,gpstime,gpsdate,orientationNS,orientationEV,direction,speed,distance) def handle_read(self): data = self.recv(8192) data_string = data.decode(errors="ignore") #print(len(data_string)) if len(data_string) >= 84 and len(data_string) <= 88 : data_tuple = self.GetData(data_string) print(data_tuple) Database.dbinsert(data_tuple) class DataServer(asyncore.dispatcher): def __init__(self, host, port): asyncore.dispatcher.__init__(self) self.create_socket(socket.AF_INET, socket.SOCK_STREAM) self.set_reuse_addr() self.bind((host, port)) self.listen(5) def handle_accept(self): pair = self.accept() if pair is None: return else: sock, addr = pair print('Get data from %s' % repr(addr)) handler = ClientHandler(sock) database=Database() server = DataServer('192.168.10.10', 21112) asyncore.loop() RE: Python 3 MySQL database insert error - Larz60+ - Aug-01-2017 Please show the traceback (full error messages), verbatim as we are all used to the format, and it contains valuable information that will help to pinpoint the problem RE: Python 3 MySQL database insert error - georgian2all - Aug-01-2017 That's all that I have. That's the entire error message. It's in quotes before my script. (Aug-01-2017, 11:14 AM)Larz60+ Wrote: Please show the traceback (full error messages), verbatim This is the full error message. I'm sorry I didn't list entirely from the beginning. Quote:Get data from ('tracker_ip', 25904) ('tracker_id', latitude, longitude, '102456', '010817', 'N', 'E', '0', '0.00', 10.3) error: uncaptured python exception, closing channel <main.EchoHandler connected tracker_ip:25904 at 0x7f627f6e2b38> (:dbinsert() missing 1 required positional argument: 'data_tuple' [/usr/lib/python3.5/asyncore.py|read|83] [/usr/lib/python3.5/asynco re.py|handle_read_event|423] [server3_1.py|handle_read|84]) RE: Python 3 MySQL database insert error - Larz60+ - Aug-01-2017 But there is no line 129 in the code you supplied, RE: Python 3 MySQL database insert error - georgian2all - Aug-01-2017 (Aug-01-2017, 11:46 AM)Larz60+ Wrote: But there is no line 129 in the code you supplied, I think it's about line 129 from /usr/lib/python3.5/asyncore.py. File "server3_1.py", line 129, in <module> asyncore.loop() . RE: Python 3 MySQL database insert error - Larz60+ - Aug-01-2017 It's saying that there is no data in data_tuple. You have a print statement (line 76) just prior to the attempted database insert. Did that look ok? please post that. Also, I would cut and paste that SQL and try to run it from psql or whatever you use for MySQL manual queries. RE: Python 3 MySQL database insert error - georgian2all - Aug-01-2017 (Aug-01-2017, 02:03 PM)Larz60+ Wrote: It's saying that there is no data in data_tuple. That print statement before database insert looks ok and if I take that code ant run alone, works like a charm. Print statement result: ('165503060048809', 41.22235663738393, 20.966798866616260, '172534', '010817', 'N', 'E', 0, '0.00', 10.3)Mysql table structure and tuple data type Quote:signature char(50) ---- <class 'str'> RE: Python 3 MySQL database insert error - Larz60+ - Aug-01-2017 creat a backup first Change the dbinsert function to: def dbinsert(self, data_tuple): cmd = 'INSERT INTO GPS (signature, latitude, longitude, gpstime, gpsdata, orientationNS, orientationEV, direction, speed, distance) VALUES (' for n, element in enumerate(data_tuple): if n > 0: cmd = '{}, {}'.format(cmd, element) else: cmd = '{}{}'.format(cmd, element) cmd = '{})'.format(cmd) print(cmd) self.cursor.execute(sql) self.dbconnection.commit()and try that |