Python Forum
Support required to data log in SQL server single table via raspbeery pi
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Support required to data log in SQL server single table via raspbeery pi
#1
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()
Reply
#2
What do you expect // is doing? line 21 is not a valid coce. Just remove //
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
(Jun-23-2020, 04:27 PM)buran Wrote: 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'
Reply
#4
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#5
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.
I welcome all feedback.
The only dumb question, is one that doesn't get asked.
My Github
How to post code using bbtags


Reply
#6
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#7
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)
I welcome all feedback.
The only dumb question, is one that doesn't get asked.
My Github
How to post code using bbtags


Reply
#8
(Jun-23-2020, 05:00 PM)buran Wrote: 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 ?
Reply
#9
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Using pyodbc&pandas to load a Table data to df tester_V 3 746 Sep-09-2023, 08:55 PM
Last Post: tester_V
  Need help on how to include single quotes on data of variable string hani_hms 5 1,883 Jan-10-2023, 11:26 AM
Last Post: codinglearner
  In SQL Server, mix data types. shiv11 0 852 Sep-21-2022, 12:50 PM
Last Post: shiv11
  How to modify python script to append data on file using sql server 2019? ahmedbarbary 1 1,175 Aug-03-2022, 06:03 AM
Last Post: Pedroski55
  panda table data kucingkembar 0 1,072 Mar-01-2022, 10:38 PM
Last Post: kucingkembar
  Sorting table data Blacktime2 1 1,289 Feb-26-2022, 07:05 PM
Last Post: ibreeden
  Strategy on updating edits back to data table and object variables hammer 0 1,163 Dec-11-2021, 02:58 PM
Last Post: hammer
  SQLALCHEMY - Not selecting data from table jamesaarr 4 2,178 Nov-02-2021, 03:02 PM
Last Post: Larz60+
  Presenting multiline data into single line aaronbuhu 1 1,768 Aug-05-2021, 10:57 AM
Last Post: jamesaarr
  How to take the tar backup files form remote server to local server sivareddy 0 1,871 Jul-14-2021, 01:32 PM
Last Post: sivareddy

Forum Jump:

User Panel Messages

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