0

I have troubles while fetching data from a SQL-Database. The program gets data from a PHP-site and plays the sound if it is a youtube link. Currently, it has troubles when the database is empty. My goal is, that it exits the for-loop, what it does, but it does not receive any data and tries to fetch it once more until data is inserted by the site.

import mysql.connector
import pafy
import vlc
import time
from mysql.connector.errors import Error



url = "NULL"

try:
        connection = mysql.connector.connect(host='localhost',
                                             database='musicbox',
                                             user='',
                                             password='')
        if connection.is_connected():
                db_Info = connection.get_server_info()
                print("Connected to MySQL Server version ", db_Info)
                cursor = connection.cursor()
                while True :

                        db_Info = connection.get_server_info()
                        print("Connected to MySQL Server version ", db_Info)

                        cursor = connection.cursor()
                        print "test2"
                        cursor.execute("SELECT * FROM playlist")
                        record = cursor.fetchall()
                        print record
                        time.sleep(1)
                        for x in record:
                                print x[0]
                                if "youtube" in x[0]:
                                        if not all(x):
                                                print "empty"
                                        else:
                                                print "true"
                                                url = x[0]
                                                video = pafy.new(url)
                                                best = video.getbestaudio()
                                                playurl = best.url
                                                Instance = vlc.Instance()
                                                player = Instance.media_player_new()
                                                Media = Instance.media_new(playurl)
                                                Media.get_mrl()
                                                player.set_media(Media)
                                                player.play()
                                                time.sleep(1.5)
                                                duration = player.get_length() / 1000
                                                time.sleep(duration)
                                                cursor.execute("DELETE FROM playlist LIMIT 1")
                                                connection.commit()

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")
double-beep
  • 5,031
  • 17
  • 33
  • 41
PixelWelt
  • 1
  • 2
  • Try doing `connection.commit()` before executing the `SELECT`. – snakecharmerb Sep 19 '20 at 07:07
  • Also `"DELETE FROM playlist LIMIT 1"` on my database deletes the first record from the target table, which may not be the behaviour that you want. Something like `cursor.execute("DELETE FROM playlist WHERE url = %s LIMIT 1", (x[0],))` might be better. Assuming that you want to delete the row after processing it. – snakecharmerb Sep 19 '20 at 07:45
  • Thanks a lot @snakecharmerb your solution with connection.commit() works just fine. – PixelWelt Sep 19 '20 at 11:25
  • 1
    [Here](https://stackoverflow.com/questions/61159072/repeated-mysql-queries-from-python/61161279?r=SearchResults&s=5|0.0000#61161279) is an answer that explains what is probably happening. – snakecharmerb Sep 19 '20 at 13:20

0 Answers0