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 and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  sqlite3 Conn Insert Value Error TylerDunbar 3 676 Sep-04-2023, 06:32 PM
Last Post: deanhystad
  Insert 10gb csv files into sql table via python mg24 2 1,833 Apr-28-2023, 04:14 PM
Last Post: snippsat
  Python Serial: How to read the complete line to insert to MySQL? sylar 1 787 Mar-21-2023, 10:06 PM
Last Post: deanhystad
  python insert blank line in logger mg24 1 2,730 Nov-02-2022, 08:36 AM
Last Post: snippsat
  error 1102 (42000) incorrect database name 's' Anldra12 4 1,651 Jun-08-2022, 09:00 AM
Last Post: Anldra12
  Mysql Syntax error in pymysql ilknurg 4 2,291 May-18-2022, 06:50 AM
Last Post: ibreeden
  Insert a multiple constant value after header in csv file using python shantanu97 1 1,114 Apr-24-2022, 10:04 AM
Last Post: Pedroski55
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,685 Feb-09-2022, 09:55 AM
Last Post: ibreeden
Question Debian 11 Bullseye | Python 3.9.x | pip install mysql-connector-python-rf problems BrandonKastning 4 6,574 Feb-05-2022, 08:25 PM
Last Post: BrandonKastning

Forum Jump:

User Panel Messages

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