'Insert into SQL database using Python

I want to put a value from my Raspberry Pi into a SQL database on my laptop by using websocket in a Python script.

#starting import MySQLdb

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","root","","cms",autocommit=True)

# prepare a cursor object using cursor() method
cursor = db.cursor()

from websocket import create_connection

ws = create_connection("ws://192.168.0.53:8080/websocket")

while True:
    result = ws.recv()
    print(result)
    spl = result.split(' ',1)
    boom_id2 = str(spl[0])
    boom_data = str(spl[1])



    sql = """INSERT INTO datain(boom_id2,boom_data)
             # VALUES ('""" + boom_id2 +"""', '""" + boom_data +"""')"""
    cursor.execute(sql)


db.close()
ws.close()

It returns an error:

Traceback (most recent call last):

  File "C:\Users\Ashraff\Desktop\Enginnering\test.py", line 26, in <module>
    cursor.execute(sql)
  File "C:\Users\Ashraff\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "C:\Users\Ashraff\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "C:\Users\Ashraff\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 247, in execute
    res = self._query(query)
  File "C:\Users\Ashraff\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "C:\Users\Ashraff\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 374, in _do_query
    db.query(q)
  File "C:\Users\Ashraff\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\connections.py", line 277, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2")
mysqlsqlpython-3.xwebsocketraspberry-pi


Solution 1:[1]

There's an error in your SQL query it should be something like this

   sql = ("""INSERT INTO datain(boom_id2,boom_data)
            VALUES (%s, %s)""", (boom_id2, boom_data))
   print(sql)
   cursor.execute(sql)

try to print this SQL query and run it in your local server to check whether your query is correct or not

Solution 2:[2]

def insert_data(lVideoList, gate_id):
    connection = sqlite3.connect('db.sqlite',
                                 detect_types=sqlite3.PARSE_DECLTYPES |
                                              sqlite3.PARSE_COLNAMES)
    cursor = connection.cursor()
    success = 200
##    gateid = 1
    default_value = 0
    for gate_id in range(no_of_gate):
        gate_id = i+1
        for videofilename in lVideoList:
            print("videofilename: ", videofilename)
            insert_query = ("INSERT INTO dailyfootfall(csv_name, video_download, processed, footfall, send_status, "
                            "male_footfall, send_status_male, female_footfall, send_status_female, gate_id,outsiders, send_status_outsiders) "
                            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)")
            
            cursor.execute(insert_query,[videofilename, success, success, default_value, success, default_value,
                                        success, default_value, success, gate_id, default_value, success])

       
    print("Data Inserted Successfully !")
    connection.commit()
    cursor.close()
    connection.close()

if __name__ == "__main__":

    lVideoList = getCompleteVideoList("2022_01_24", "10:00", "22:00")
    no_of_gate = 3
    insert_data (lVideoList, gate_id)
    print("default_value inserted!!!!")

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Meghana Goud
Solution 2 24_saurabh sharma

Related Questions