Trouble inserting values into mysql server using mysql.connector

Question

I am using the following function in class implementation to insert values username_s , ipadress_s, and so on MYSQL database with column username , ipadress and so on but I keep getting the following error

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'username_s' in 'field list'

The confusion is the interchange of column names with the column values

def insert_server(self , username_s , ipadress_s , BLACKLISTED_s , clientid_s):
    self.connect.database = self.m_database

    self.cursor.execute(
        "INSERT INTO server"
        " ( username , ipadress , blacklisted , clientid )"
        "VALUES ( username_s , ip_adress_s , BLACKLISTED_s , clientid_s)")

WolfgangK · Accepted Answer · 2018-02-23 09:42:45Z

MySQL will interpret 'username_s' and the other names in VALUES as literal column names because there are no format codes indicating that these are actually variables. You can take a look at the example in the MySQL documentation Inserting data using Connector/Python. For clarification, here is a short MWE:

import sqlalchemy

# insert connection details
engine = sqlalchemy.create_engine('<YourConnection>')

# insert statement with variable format coding in VALUES
add_client = (
    'INSERT INTO server'
    ' (username, ipadress, blacklisted, clientid )'
    ' VALUES ( %s, %s, %s, %s)')

# invent some client data
username_s = 'Mary'
ipadress_s = '321.45.67.89'
BLACKLISTED_s = True
clientid_s = 123456
client_data = (username_s, ipadress_s, BLACKLISTED_s, clientid_s)

with engine.connect() as cnx:
    cnx.execute(add_client, client_data)

Collectives™ on Stack Overflow

Trouble inserting values into mysql server using mysql.connector

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related