如何从mysql数据库中选择一行时，如何执行一段python代码

Question

I've built a set of musical stairs with python, motion sensors and a raspberry pi, and a web app that lets you choose which type of instrument sound you want to make. The type of instrument is stored in a MySQL database which I have connected to the python code (which makes the sounds when a beam is broken) and to a web app which allows users to select the instrument type from the database.

I am just wondering is there a way of querying the database from the python code that would mean only when a row is selected from the database, run a particular block of code.

Eg, someone clicks "Drum" on the web app. instrumentType "Drum" is selected from MySQL database Drumsound.play() should run on the python code.

Is there any way I could do this on python?

This is for a raspberry pi 3 running python 2.7, mySQLdb5 and apache2.

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="*****",
    pw="*****",
    db="stairs"
    )

cursor = mydb.cursor()
cursor.execute("SELECT variableValue FROM stairs WHERE variableValue = 
'instrumentType'")

import RPi.GPIO as GPIO # GPIO
import pygame.mixer # To make sound
pygame.mixer.init()

''' GPIO setup '''
GPIO.setmode(GPIO.BCM) # GPIO setmode
GPIO.setwarnings(False)

'''Define steps and pins here'''
step1 = 4


'''Motion sensor setup here'''
GPIO.setup(step1, GPIO.IN, GPIO.PUD_UP)


'''Piano files here'''
C1 = pygame.mixer.Sound("piano/C1.wav")


'''Drum files here'''
drum1 = pygame.mixer.Sound("drum/C1.wav")



def play(pin):
    sound = sound_pins[pin]
    print("Playing note from pin %s" % pin)
    sound.play()

'''Dictionary of steps and sounds'''
sound_pins = {
    step1: C1,
    step2: D,
    step3: E,
    step4: F,
    step5: G,
    step6: A,
    step7: B,
    step8: C2,
    }

for pin in sound_pins:
        GPIO.setup(pin, GPIO.IN, GPIO.PUD_UP)
        GPIO.add_event_detect(pin, GPIO.RISING, play, 100)

Answer 1

You might consider using a python dictionary to store your 'instrument' type, against a function. This allows you to create a mapping of which instrument to which sound play.

You might be asking yourself, "this sounds like what I want my database to do?". As you use the same pygame.mixer.Sound("SomeWavePath") you could store this relationship between instrument names and their sound files in the database itself. This way you can expand your instrument selection by just adding to your database.

May I also recommend making the switch over to Python 3.x, as 2.x is shortly coming to an end of support (https://pythonclock.org/). This will also give you access to new language features and a wider range of library support moving forwards.

EDIT:

E.g Storing "instrument<->wav_path" mapping in your database.

# Query executing obtains 'wav path' from 'instrument' primary key.
choice = cur.fetchone()  #  "piano/C1.wav"
sound_to_play = pygame.mixer.Sound(choice)

E.g Attaching objects against your instruments

C1 = pygame.mixer.Sound("piano/C1.wav")
drum1 = pygame.mixer.Sound("drum/C1.wav")

instrument_dict = {
    "piano": C1,
    "drum": drum1
}

# Retrieve the sound to play
choice = cur.fetchone()  # From our database, get 'drum' for example
sound_to_play = instrument_dict[ choice ]
# instrument_dict[ 'drum' ] => drum1 Sound Object.

Then:

sound_to_play.play()