INSERT function doesn't throw errors, still not updating DB table

Question

I am creating a Mysql Wrapper for Python, mainly just to learn the ins and outs of the language etc. Though I stumble upon an issue I can't see to find a solution for.

When I run the script, there are no exceptions thrown, though my DB doesn't get updated. I played around with the DB Cursors, like closing them, creating new one in the function etc. But the result remains the same.

aka: cursor.rowcount -1

Connecting to the DB:

class magicDB:
    myDB = None
    DB_Cursor = None

    def __init__(self,host, user, passwd, DB=None):
        global myDB
        self.host = host
        self.user = user
        self.DB = DB

        if self.DB is None:
            myDB = mysql.connector.connect(
                host= host,
                user= user,
                passwd= passwd)
            #DB_Cursor = myDB.cursor()
        else:
            myDB = mysql.connector.connect(
                host= host,
                user= user,
                passwd= passwd,
                database=DB)
            #DB_Cursor = myDB.cursor()
    def insert(self,table_name, params):


            fields = ''
            values = ''
            placeholders = ''

            query = '"INSERT INTO {} '. format(table_name)
            for key in params:
                fields = fields + str(key) + ', '
                values = values + str(params[key]) + ', '
                placeholders = placeholders + str('%s, ')

            fields = fields[:-2]
            values = values[:-2]
            placeholders = placeholders[:-2]
            query = query + '(' + fields + ')' + ' VALUES ' + '(' + placeholders + ')"'
            print(query)
            try:
                Cursor = myDB.cursor()
                Cursor.execute(operation=query, params=values, multi=True)
                myDB.commit()
                print(Cursor.rowcount, ' Record inserted')
            except mysql.connector.Error as error:
                print (error)
            finally:    
                Cursor.close()

And this is the main module that calls the functions:

from db_magic import magicDB

conn = magicDB(host='localhost', user='***', passwd='****', DB='testing')
print(conn)


# print(conn.DB_create('testing2'))


conn.insert(table_name= 'users', params={
    'user_name': 'John Doe',
    'email' : '[email protected]',
    'password': 'test'

} )
print(conn.Close_conn())

I am trying to understand what I am doing wrong here, as there are no exceptions thrown.

SOLUTION:

Changed the values to a list instead of a string.

def insert(self,table_name, params):      
        fields = ''
        values = [] # changed to List instead of String
        placeholders = ''

        query = 'INSERT INTO {} '. format(table_name)
        for key in params:
            fields = fields + str(key) + ', '
            values.append(params[key])
            placeholders = placeholders + str('%s, ')

        fields = fields[:-2]
        placeholders = placeholders[:-2]
        query = query + '(' + fields + ')' + ' VALUES ' + '(' + placeholders + ')'
        print(query)
        print(values)
        try:
            Cursor = myDB.cursor()
            Cursor.execute(operation=query, params=values, multi=False)
            myDB.commit()
            print(Cursor.rowcount, ' Record inserted')
        except mysql.connector.Error as error:
            print(error)
        except:
            print ('Some errors')
        finally:    
            Cursor.close()

you need to do conn.commit() to actually save all the changes — dangee1705
– dangee1705, Commented Jun 22, 2019 at 10:04
Yeah, I am calling myDB.commit() after the execute function in the 'Try / Except' statement. — Oclee
– Oclee, Commented Jun 22, 2019 at 10:07
Have you tried changing except mysql.connector.Error as error to except Exception as error to check that there is not an error of a different type? — dangee1705
– dangee1705, Commented Jun 22, 2019 at 10:09
That's good thinking indeed. Changed it to just an except: and print('errors thrown') but still same outcome unfortunate, no errors. — Oclee
– Oclee, Commented Jun 22, 2019 at 10:12
I see there are several print statements in the code - what output do they produce when you run it? — hifkanotiks
– hifkanotiks, Commented Jun 22, 2019 at 10:14

Oclee · Accepted Answer · 2019-06-22 11:28:01Z

The Values variable was used as a string, which MYSQL - connector doesn't iterate through. To solve this I change the Values Variable to a list:

def insert(self,table_name, params):      
        fields = ''
        values = [] # changed to List instead of String
        placeholders = ''

        query = 'INSERT INTO {} '. format(table_name)
        for key in params:
            fields = fields + str(key) + ', '
            values.append(params[key])
            placeholders = placeholders + str('%s, ')

        fields = fields[:-2]
        placeholders = placeholders[:-2]
        query = query + '(' + fields + ')' + ' VALUES ' + '(' + placeholders + ')'
        print(query)
        print(values)
        try:
            Cursor = myDB.cursor()
            Cursor.execute(operation=query, params=values, multi=False)
            myDB.commit()
            print(Cursor.rowcount, ' Record inserted')
        except mysql.connector.Error as error:
            print(error)
        except:
            print ('Some errors')
        finally:    
            Cursor.close()

Collectives™ on Stack Overflow

INSERT function doesn't throw errors, still not updating DB table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related