vajra11
Posts: 101
Joined: Thu Jul 05, 2018 4:44 am

Pushing data to MySQL using Pi

Mon Sep 10, 2018 7:16 am

Hi,

I have a MySQL server installed on my window computer. I have tested connection between Pi and MySQL successfully with following program

Code: Select all

import mysql.connector

db = mysql.connector.connect(user='database1', password='****',
                              host='192.168.0.111',
                              database='test3') 
# Create a Cursor object to execute queries.
cur = db.cursor()
 
# Select data from table using SQL query.
cur.execute("SELECT * FROM tablename")
 
# print the first and second columns    
    
for row in cur.fetchall() :
    print row[0], " ", row[1]
$ python dbtest1.py
1 Sample data

I have a MCP3008 ADC chip and raspberry Pi 3 I want to store data of sensor into MySQL database on my computer over the time.

How to set database connection to store sensor data into MySQL database in below program

Code: Select all

# Simple example of reading the MCP3008 analog input channels and printing
# them all out.
# Author: Tony DiCola
# License: Public Domain
import time
import datetime

# Import SPI library (for hardware SPI) and MCP3008 library.
import Adafruit_GPIO.SPI as SPI
import Adafruit_MCP3008


# Software SPI configuration:
CLK  = 11
MISO = 9
MOSI = 10
CS   = 8
mcp = Adafruit_MCP3008.MCP3008(clk=CLK, cs=CS, miso=MISO, mosi=MOSI)
print(datetime.datetime.now())
# Hardware SPI configuration:
# SPI_PORT   = 0
# SPI_DEVICE = 0
# mcp = Adafruit_MCP3008.MCP3008(spi=SPI.SpiDev(SPI_PORT, SPI_DEVICE))


print('Reading MCP3008 values, press Ctrl-C to quit...')
# Print nice channel column headers.
print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*range(8)))
print('-' * 57)
# Main program loop.
while True:
    # Read all the ADC channel values in a list.
    values = [0]*8
    for i in range(8):
        # The read_adc function will get the value of the specified channel (0-7).
        values[i] = mcp.read_adc(i)
    # Print the ADC values.
    print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*values))
    # Pause for half a second.
    time.sleep(0.5)

W. H. Heydt
Posts: 13345
Joined: Fri Mar 09, 2012 7:36 pm
Location: Vallejo, CA (US)

Re: Pushing data to MySQL using Pi

Mon Sep 10, 2018 1:41 pm

The ID and password you are using to connect to the remote database has to have INSERT permission granted for the table in question. Then you build a string with all the values you need to write the table row. Call the database using the string you've built. Check for errors.

vajra11
Posts: 101
Joined: Thu Jul 05, 2018 4:44 am

Re: Pushing data to MySQL using Pi

Mon Sep 10, 2018 2:15 pm

W. H. Heydt wrote:
Mon Sep 10, 2018 1:41 pm
The ID and password you are using to connect to the remote database has to have INSERT permission granted for the table in question. Then you build a string with all the values you need to write the table row. Call the database using the string you've built. Check for errors.
I don't know much about Mysql program. I need help in Mysql program

Code: Select all

 
+-----------------+--------------+------+-----+-------------------+-------+
| Field           | Type         | Null | Key | Default           | Extra |
+-----------------+--------------+------+-----+-------------------+-------+
| Data0           | varchar(50)  | NO   |     | NULL              |       |
| Data1           | varchar(50)  | NO   |     | NULL              |       |
| Data2           | varchar(50)  | NO   |     | NULL              |       |
| Data3           | varchar(50)  | NO   |     | NULL              |       |
| Data4           | varchar(50)  | NO   |     | NULL              |       |
| Data5           | varchar(50)  | NO   |     | NULL              |       |
| Data6           | varchar(50)  | NO   |     | NULL              |       |
| Data7           | varchar(50)  | NO   |     | NULL              |       |
+-----------------+--------------+------+-----+-------------------+-------+

Code: Select all

import mysql.connector
import time
import datetime
import string


# Import SPI library (for hardware SPI) and MCP3008 library.
import Adafruit_GPIO.SPI as SPI
import Adafruit_MCP3008


# Software SPI configuration:
CLK  = 11
MISO = 9
MOSI = 10
CS   = 8
mcp = Adafruit_MCP3008.MCP3008(clk=CLK, cs=CS, miso=MISO, mosi=MOSI)

db = mysql.connector.connect(user='database1', password='****',
                              host='192.168.0.111',
                              database='test3') 
							  
# Create a Cursor object to execute queries.
cur = db.cursor()
 
# Select data from table using SQL query.
cur.execute("SELECT * FROM tablename")
  
# print the first and second columns    
    
for row in cur.fetchall() :
    print row[0], " ", row[1]
	

pfletch101
Posts: 629
Joined: Sat Feb 24, 2018 4:09 am
Location: Buffalo, NY, USA

Re: Pushing data to MySQL using Pi

Mon Sep 10, 2018 3:34 pm

vajra11 wrote:
Mon Sep 10, 2018 2:15 pm
W. H. Heydt wrote:
Mon Sep 10, 2018 1:41 pm
The ID and password you are using to connect to the remote database has to have INSERT permission granted for the table in question. Then you build a string with all the values you need to write the table row. Call the database using the string you've built. Check for errors.
I don't know much about Mysql program. I need help in Mysql program
Then I am afraid that you need to spend some time learning at least the basics of database management with MySQL. I doubt that anyone here has the time or energy to tutor you. Googling "MySQL Introduction" produces a lot of worthwhile material.

vajra11
Posts: 101
Joined: Thu Jul 05, 2018 4:44 am

Re: Pushing data to MySQL using Pi

Tue Sep 11, 2018 6:16 pm

pfletch101 wrote:
Mon Sep 10, 2018 3:34 pm

Then I am afraid that you need to spend some time learning at least the basics of database management with MySQL. I doubt that anyone here has the time or energy to tutor you. Googling "MySQL Introduction" produces a lot of worthwhile material.
Hope now someone can help I checked table data isn't inserting into database

Code: Select all

import time
import datetime
import mysql.connector
# Import SPI library (for hardware SPI) and MCP3008 library.
import Adafruit_GPIO.SPI as SPI
import Adafruit_MCP3008


# Software SPI configuration:
CLK  = 11
MISO = 9
MOSI = 10
CS   = 8
mcp = Adafruit_MCP3008.MCP3008(clk=CLK, cs=CS, miso=MISO, mosi=MOSI)
print(datetime.datetime.now())
# Hardware SPI configuration:
# SPI_PORT   = 0
# SPI_DEVICE = 0
# mcp = Adafruit_MCP3008.MCP3008(spi=SPI.SpiDev(SPI_PORT, SPI_DEVICE))

db = mysql.connector.connect(user='db1', password='***',
                              host='192.168.0.108',
                              database='test3') 

# Create a Cursor object to execute queries.
cur = db.cursor()
cur.execute('SELECT * from t1')

for row in cur:
    print('%r' % (row,))

print('Reading MCP3008 values, press Ctrl-C to quit...')
# Print nice channel column headers.
print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*range(8)))
print('-' * 57)
# Main program loop.
while True:
    # Read all the ADC channel values in a list.
    values = [0]*8
    for i in range(8):
        # The read_adc function will get the value of the specified channel (0-7).
        values[i] = mcp.read_adc(i)
    # Print the ADC values.
    print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*values))
    # Pause for half a second.
    time.sleep(0.5)
	cur.execute("""INSERT INTO t1(data1, data2, data3, data4, data5, data6, data7, data8)values(%s,%s,%s,%s,%s,%s,%s,%s)""",(values[0],values[1],values[2],values[3]),value[4],value[5],value[6],value[7])
    cur.execute('SELECT * FROM t1')   
    for row in cur.fetchall() :
        print('%r' % (row,))
        time.sleep(10)
	
    

W. H. Heydt
Posts: 13345
Joined: Fri Mar 09, 2012 7:36 pm
Location: Vallejo, CA (US)

Re: Pushing data to MySQL using Pi

Tue Sep 11, 2018 7:44 pm

vajra11 wrote:
Tue Sep 11, 2018 6:16 pm
pfletch101 wrote:
Mon Sep 10, 2018 3:34 pm

Then I am afraid that you need to spend some time learning at least the basics of database management with MySQL. I doubt that anyone here has the time or energy to tutor you. Googling "MySQL Introduction" produces a lot of worthwhile material.
Hope now someone can help I checked table data isn't inserting into database

Code: Select all

import time
import datetime
import mysql.connector
# Import SPI library (for hardware SPI) and MCP3008 library.
import Adafruit_GPIO.SPI as SPI
import Adafruit_MCP3008


# Software SPI configuration:
CLK  = 11
MISO = 9
MOSI = 10
CS   = 8
mcp = Adafruit_MCP3008.MCP3008(clk=CLK, cs=CS, miso=MISO, mosi=MOSI)
print(datetime.datetime.now())
# Hardware SPI configuration:
# SPI_PORT   = 0
# SPI_DEVICE = 0
# mcp = Adafruit_MCP3008.MCP3008(spi=SPI.SpiDev(SPI_PORT, SPI_DEVICE))

db = mysql.connector.connect(user='db1', password='***',
                              host='192.168.0.108',
                              database='test3') 

# Create a Cursor object to execute queries.
cur = db.cursor()
cur.execute('SELECT * from t1')

for row in cur:
    print('%r' % (row,))

print('Reading MCP3008 values, press Ctrl-C to quit...')
# Print nice channel column headers.
print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*range(8)))
print('-' * 57)
# Main program loop.
while True:
    # Read all the ADC channel values in a list.
    values = [0]*8
    for i in range(8):
        # The read_adc function will get the value of the specified channel (0-7).
        values[i] = mcp.read_adc(i)
    # Print the ADC values.
    print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*values))
    # Pause for half a second.
    time.sleep(0.5)
	cur.execute("""INSERT INTO t1(data1, data2, data3, data4, data5, data6, data7, data8)values(%s,%s,%s,%s,%s,%s,%s,%s)""",(values[0],values[1],values[2],values[3]),value[4],value[5],value[6],value[7])
    cur.execute('SELECT * FROM t1')   
    for row in cur.fetchall() :
        print('%r' % (row,))
        time.sleep(10)
	
    
The first thing I would do is put some white space around the parameters and and a statement terminator (';') at the end, so that the string reads "INSERT INTO t1 (data1, data2, ...) values (%s, %s, ...);". The next thing I would do would be to check for returned errors. In C, that is done with:

Code: Select all

   if(mysql_query(conn,q_string) != 0)
   {
      print_dberr(conn,"Attendee Master insert failed");
      return;
   }
The best thing to do, of course, since your insert *is* failing, is to capture the actual MySQL error message and print that rather than a message you have composed.

Return to “Beginners”