Issue in python code and MYSQL database.

Topics about the Software of Revolution Pi
Post Reply
mayuresh jagtap
Posts: 1
Joined: 17 Feb 2020, 13:05
Answers: 0

Issue in python code and MYSQL database.

Post by mayuresh jagtap »

hello sir,
I have created mysql database in python3 , but it is causing issue in database . actually my code is right but it is causing error. my questions are .
1. How to create new table in mysql database .?
2. How to create new database ?
3. my Python code given below is occuring error how to sort it out.?
##################################################################################################
# This Example save the data of sensor values into RevPi using Mysql database.
# The sensor data is get/generated using the modsim simulator.
# Author-Anil Ramesh Sarode TO@ULEPL
###################################################################################################

import time
import datetime
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
import revpimodio2 # To read the pins of revPi

revpi = revpimodio2.RevPiModIO(autorefresh=True) # Instance to access to the IOs of the RevPiimport revpimodio2

connection = mysql.connector.connect(host='localhost',
database='sensor',
user='root',
password='UL@123')

ti = time.gmtime()
def dateTime(): #get UNIX time
secs = time.asctime(ti)
# secs = secs*1000
return secs

def tempRead(): #read temperature, return float with 3 decimal places
degrees = float('{0:.3f}'.format(revpi.io.Input_Word_1.value))
return degrees

def pressRead():#read pressure, return float with 3 decimal places
pascals = float('{0:.3f}'.format(revpi.io.Input_Word_2.value/100))
return pascals

def humidityRead(): #read humidity, return float with 3 decimal places
humidity = float('{0:.3f}'.format(revpi.io.Input_Word_3.value))
return humidity

secs = dateTime()
temperature = tempRead()
pressure = pressRead()
humidity = humidityRead()
#ID_number=1

try:
mySql_insert_query= """INSERT INTO Sensor_Modbus_TCP_Data (datetime,temperature,pressure,humidity) VALUES (%s,%s,%s,%s)"""
args =(secs, temperature, pressure, humidity)

cursor = connection.cursor()
# result = cursor.execute(mySql_Create_Table_Query)
# print("Sensor_ModbusTCP_Data Table created successfully ")

cursor.execute(mySql_insert_query,args)
connection.commit()
print(cursor.rowcount, "Record inserted successfully into Sensor_ModbusTCP_Data table")
cursor.close()

except mysql.connector.Error as error:
# print("Failed to create table in MySQL: {}".format(error))
print("Failed to insert record into Sensor_ModbusTCP_Data table {}".format(error))

finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed")

print (secs)
print (temperature)
print (pressure)
print (humidity)
User avatar
nicolaiB
KUNBUS
Posts: 869
Joined: 21 Jun 2018, 10:33
Answers: 7
Location: Berlin
Contact:

Re: Issue in python code and MYSQL database.

Post by nicolaiB »

Hi,

for the first two of your questions have a look into the getting started section of the mysql documentation:

https://dev.mysql.com/doc/mysql-getting-started/en/

The third question needs better explanation. What is your error message / the problem?

Nicolai
cristydavidd
Posts: 1
Joined: 08 Nov 2022, 07:39
Answers: 0

Re: Issue in python code and MYSQL database.

Post by cristydavidd »

In some of the ways, spacing and the order of parameters in the MySql connection string does matters. So, stick to the standard format:

MysqlConn.ConnectionString = "Server=localhost;Port=1234;Database=My_Mysql_Database;Uid=root;Pwd=root;"

If the above connection string fails, try update your c# mysql connection string as shown below (without port variable as well):

MysqlConn.ConnectionString = "Server=localhost;Database=My_Mysql_Database;Uid=root;Pwd=root;"

Or, sometime the problem could be on your windows firewall, make sure your server allow access to all port associated with your [url=http://net-informations.com/q/faq/mysql.html]mysql[/url] database.
Post Reply