CODEWITHSUNDEEP
pythonmysqlpython-3.xparsingmysql-python
S Dubovskoy
S Dubovskoy

Reputation: 15

Why am I getting an error on MySQL syntax?

Help me please. I am writing a cdr avaya log parser with writing data to the mysql database. The parser itself works fine, but it doesn’t write to the database. The program itself is written in python 3.7 and is divided into 2 files. The cdr.py parser itself and the db.py DB record file. It produces such an error:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-dur, in-trk-code, in-crt-id, code-used, out-crt-id, clg-num-in-tag, dialed-num,' at line 1")

All table fields are of type INT with the ability to write NULL.

cdr.py

import socket
import db 

# Задаем адрес сервера
SERVER_ADDRESS = ('', 5100)

# Настраиваем сокет
server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
server_socket.bind(SERVER_ADDRESS)
server_socket.listen(5)
print('server is running, please, press ctrl+c to stop')

# Слушаем запросы
while True:
    connection, address = server_socket.accept()

    data = connection.recv(1024)
    if not(b'\x00\x00\x00' in data):
        str = data.decode("utf-8")
        item=(str[0:6],str[7:11],str[12:17],str[18:22],str[23:26],str[27:30],str[31:34],str[35:50],str[51:74],str[75:76],str[77:90],str[91:92])
        print(item)
        db.write_db(item)

    connection.close()

db.py

import pymysql.cursors

def write_db(item, *agrs):
    connection = pymysql.connect(host='localhost',
                                 user='acdr',
                                 password='it8ejokd',
                                 db='avaya_cdr',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    DBTBL = "cdr102019"
    DBFLD = "Date, Time, Sec-dur, in-trk-code, in-crt-id, code-used, out-crt-id, clg-num-in-tag, dialed-num, cond-code, vdn, frl"


    try:
        with connection.cursor() as cursor:
            sql = "INSERT INTO "+DBTBL+" ("+DBFLD+") VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            cursor.execute(sql, (item))
        connection.commit()
    finally:
        connection.close()

Upvotes: 0

Views: 109

Answers (1)

Barmar
Barmar

Reputation: 780714

You need to put backticks around column names that contain -.

DBFLD = "Date, Time, `Sec-dur`, `in-trk-code`, `in-crt-id`, `code-used`, `out-crt-id`, `clg-num-in-tag`, `dialed-num`, `cond-code`, vdn, frl"

For more details, see When to use single quotes, double quotes, and backticks in MySQL

Upvotes: 2

Related Questions