Python Forum
Python 3 MySQL database insert error
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python 3 MySQL database insert error
#1
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()
Reply
#2
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
Reply
#3
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
as we are all used to the format, and it contains valuable information
that will help to pinpoint the problem

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])
^CTraceback (most recent call last):
File "server3_1.py", line 129, in <module>
asyncore.loop()
File "/usr/lib/python3.5/asyncore.py", line 203, in loop
poll_fun(timeout, map)
File "/usr/lib/python3.5/asyncore.py", line 129, in poll
r = []; w = []; e = []
KeyboardInterrupt
Reply
#4
But there is no line 129 in the code you supplied,
Reply
#5
(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() .
Reply
#6
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.
Reply
#7
(Aug-01-2017, 02:03 PM)Larz60+ Wrote: 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.

That print statement before database insert looks ok and if I take that code ant run alone, works like a charm. Sick

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'>
latitude double ---- <class 'float'>
longitude double ---- <class 'float'>
gpstime char(10) ---- <class 'str'>
gpsdata char(10) ---- <class 'str'>
orientationNS char(1) ---- <class 'str'>
orientationEV char(1)---- <class 'str'>
direction int(11)---- <class 'str'> #was a data mismatch but I have corrected and return the same error.
speed text ---- <class 'str'>
distance double ---- <class 'float'>
Reply
#8
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  mySQL Database error not resolving. cybertooth 2 453 Aug-30-2021, 05:45 PM
Last Post: ibreeden
  Error creating database with python and form? shams 3 526 Aug-02-2021, 02:00 PM
Last Post: deanhystad
  MYSQL.CONNECTOR ERROR DB1 8 1,282 Jul-23-2021, 03:31 AM
Last Post: DB1
  pool mysql error - not catch by try\except? korenron 1 508 Jul-05-2021, 11:26 AM
Last Post: ibreeden
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 627 Jul-02-2021, 02:19 PM
Last Post: xtc14
  Python and MySql ogautier 8 1,072 May-20-2021, 11:10 PM
Last Post: Pedroski55
  UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 error from Mysql call AkaAndrew123 1 1,126 Apr-28-2021, 08:16 AM
Last Post: AkaAndrew123
  Python and MySQL Pedroski55 5 1,213 Mar-23-2021, 06:40 AM
Last Post: ndc85430
  insert row and write to xlsx in python scttfnch 0 706 Feb-28-2021, 01:19 AM
Last Post: scttfnch
  Problem updating value in MySQL database dangermaus33 1 622 Nov-24-2020, 08:32 PM
Last Post: dangermaus33

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020