Question 263548 in Mysql Question posted in
The official MySQL documentation can be found here.

How to push the data into a table from monogdb to mysql

I have data in the format below : (this is an example similar to the data i have)

playlist:{
playlist_id : ‘8DaFxSeJ0EM’,
channel_id : ‘8DUFxSeJ0EM’,
playlist_name : [name1, name2, name3…name10] }

I tried the below code but Im getting Error : cannot set options after executing query. Can anyone help me to resolve it. I have added the before snippet where the channel details are pushed to the db.

def sql_connect(sqlData) :
   
   mysql_connect = mysql.connector.connect(
        host="localhost",
        user="root",
        password="",
        database="tb_d",
        port="3306"
    )
   #Create a MySQL cursor

   mycursor = mysql_connect.cursor(buffered=True)

   #Iterate over the channels

   for channel in sqlData:

    # Extract channel information
    channel_id = channel['channel_id']['channel_id']
    channel_name = channel['channel_id']['channel_name']
    channel_views = channel['channel_id']['channel_views']
    channel_description = channel['channel_id']['channel_des']
    channel_status = channel['channel_id']['channel_status']
  
    mycursor.execute("INSERT INTO channel (channel_id, channel_name, channel_views, channel_description, channel_status) VALUES (%s, %s, %s, %s, %s)",(channel_id, channel_name, channel_views, channel_description, channel_status))
    print("Data saved to CHN TAB")  
    mysql_connect.commit() 
    mycursor.close()

    cursor_playlist = mysql_connect.cursor()

    playlist_data = sqlData['playlist_id']

    # Extract values from the data structure
 
    channel_id = playlist_data['channel_id']
    playlist_id = playlist_data['playlist_id']
    playlist_names = playlist_data['playlist_name']

    # Iterate over the playlist names and insert into the 'playlist' table  
    for playlist_name in playlist_names:

    # Insert playlist data into the 'playlist' table in MySQL
        mycursor.execute(
            "INSERT INTO playlist (playlist_id, channel_id, playlist_name) "
            "VALUES (%s, %s, %s)",
            (playlist_id, channel_id, playlist_name)
          )

    print("Data saved to playlist table")

    mysql_connect.commit()
    mycursor.close()
Tags:

2

Answers


  1. 0

    This error message typically occurs when you try to execute a query with a cursor, where the cursor has previously executed a query and hasn’t been reset. In your code, i don’t see where you created the mycursor. Check the updated code below.

    import mysql.connector

def sql_connect(sqlData):
    mysql_connect = mysql.connector.connect(
        host="localhost",
        user="root",
        password="",
        database="sample",
        port="3306"
    )

    # create a cursor 
    mycursor = mysql_connect.cursor() 

    playlist_data = sqlData['playlist_id']

    # Extract values from the data structure
    channel_id = playlist_data['channel_id']
    playlist_id = playlist_data['playlist_id']
    playlist_names = playlist_data['playlist_name']

    # Iterate over the playlist names and insert into the 'playlist' table
    for playlist_name in playlist_names:
        # Insert playlist data into the 'playlist' table in MySQL
        mycursor.execute(
            "INSERT INTO playlist (playlist_id, channel_id, playlist_name) "
            "VALUES (%s, %s, %s)",
            (playlist_id, channel_id, playlist_name)
        )

    print("Data saved to playlist table")

    # Commit the changes after the loop is finished
    mysql_connect.commit()

    # Close the cursor and the database connection
    mycursor.close()
    mysql_connect.close()

    In the above code, we first create a cursor after connecting to the DB, insert queries within the loop, commit the changes to the DB and close both the cursor and mysql connection. If it helps, pls give a tick

    Login or Signup to reply.
  2. 0

    You have already closed "mycursor", and then you are trying to use it again down the execution flow. Replace it with "cursor_playlist". Although I highly recommend to make use of transactions in such case to make sure that either all info is saved or none.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search