Support required to data log in SQL server single table via raspbeery pi

[rithikvg]

Dear All,
I have a problem while data logging in the single DB table in SQL server. We are not able to do it. When we are running the python code in raspberry pi we are getting the following error msg.

ERROR MESSAGE

Error:
File "./insertDB.py", line 19
    //cursor = con.cursor(prepared=True)
     ^
SyntaxError: invalid syntax

Please anyone help me out.

Py Code:

import serial
import time
import MySQLdb as mdb

arduino = serial.Serial("/dev/ttyACM0")
arduino.baudrate=9600

data = arduino.readline()
time.sleep(1)
data = arduino.readline()
pieces = data.split("\t")

temperature = pieces[0]
humidity = pieces[1]

#This is a comment

con = mdb.connect('localhost', 'root', '12345678', 'testdb');

with con:
  //cursor = con.cursor(prepared=True)
  sql_insert_query = """ INSERT INTO TempDB (temperature, humidity) VALUES (%s,%s)"""
 
  insert_tuple = (temperature, humidity)
  cursor.execute(sql_insert_query, insert_tuple)
  con.commit()

[buran]

What do you expect // is doing? line 21 is not a valid coce. Just remove //

[rithikvg]

What do you expect // is doing? line 21 is not a valid coce. Just remove //

Dear Friend,
Thanks for your reply. But if I remove, I am getting following message

Error Message

Traceback (most recent call last):
File "./insertDB.py", line 21, in <module>
cursor = con.cursor(prepared=True)
TypeError: cursor() got an unexpected keyword argument 'prepared'

[buran]

I didn't use MySQLdb, but in the docs I don't see example of using prepared statements.

I would recommend to use official mysql connector https://dev.mysql.com/doc/connector-python/en/ instead of MySQLdb
here is the docs about MySQLCursorPrepared class:
https://dev.mysql.com/doc/connector-pyth...pared.html

[menator01]

I have not ever been able to get the prepared statement to work. Remove the prepared part and it should work. The tuple part, should take care of the injection protection.

[buran]

Also it looks like you are using python2 - mysql-python package is targeting python2 and is not updated since 2014. Python2 support has ended. use python3

[menator01]

Here is a piece of code from one of my test projects modified. What a query would look like using pymysql

#! /usr/bin/env python3

import pymysql

host = 'localhost'
user = 'root'
password = '12345678'
database = 'testdb'
port = 3306
try:
    conn = pymysql.connect(host, user, password, database, port)
    values = (temperature, humidity)
    query = 'insert into recipes (temperature, humidity) values (%s, %s)'
    with conn.cursor() as cursor:
        cursor.execute(query, values)
        cursor.connection.commit()
except (pymysql.OperationalError, pymysql.ProgrammingError) as error:
    print(error)

[rithikvg]

Also it looks like you are using python2 - mysql-python package is targeting python2 and is not updated since 2014. Python2 support has ended. use python3

Yes you are right. If I run with python3
I am getting below error
Traceback (most recent call last):
File "./insertDB.py", line 3, in <module>
import MySQLdb as mdb
ModuleNotFoundError: No module named 'MySQLdb'

How to change this code to Python3 ?

[buran]

First of all - start using BBcode, you have been advised to do so twice
Install the "official" mysql connector as advised in my post # 4. be careful to install it for python3 - obviously you have both python2 and python3. mysql-python supports only python2 and is outdated.

full docs and examples - in the link in my previous post