ALTER TABLE to ADD COLUMN with Python variable in MySQL table after API response-CodePudding

I need to ALTER TABLE posts, ADD column product_id, and insert the product_id Python variable into my MySQL table after the API response. The output of the variable (which is returned/parsed from the response successfully) looks similar to this: prod_ABc123

I'm new here and have tried everything I could find on the Internet (including ALTER TABLE, UPDATE, and any posts I could find, etc.). Baffled why it’s not working. Can anyone help please? Everything except the ALTER TABLE works:

try:
    """
    Add user input to database
    """
    conn = get_db_connection()
    conn.execute('INSERT INTO posts (title, content, price) VALUES (?, ?, ?)',
                 (title, content, price))
    conn.commit()

    """
    Create product and pass product id and metadata price in create product response to create product price
    """
    product = Product.create(name=title, description=content, metadata={'amount': price})
    get_product_response = json.dumps(product)
    load_product_response = json.loads(get_product_response)
    product_id = load_product_response['id']
    price = load_product_response['metadata']['amount']
    Price.create(product=product_id, unit_amount=price, currency='usd')
    print(f'The {product_id} product was added with a price of ${price}.')

    """
    Add product id to database 
    """
    cur = conn.cursor()
    prod_query = "ALTER TABLE posts ADD (product_id) TEXT NOT NULL, INSERT INTO posts VALUES (?)"
    cur.execute(prod_query, product_id)
    cur.commit()

The answers provided by the posters here were very helpful!

CodePudding user response：

In relational world, ALTER TABLE is DDL command & INSERT is a DML command. You need to separate your commands by understanding the syntax.

General syntax:

--ALTER to Add column (changing table definition)

ALTER TABLE <tble_name> ADD COLUMN (<col_name> <data_type>);

--INSERT data/row

INSERT INTO <table_name> [(col1, col2, ...)] VALUES (val1, val2, ...);

A general advice, since DDL commands are usually one-time execution commands, better separate them out.

CodePudding user response：

I think if you're using MySql server, the parameter marker is %s for mysql-connector while for Microsoft SQL, the parameter marker is ? with pyodbc connector. So please take note.

For the insert statement, here it is, based on all you have told me so far:

  conn = (establish your database connection here)
 cursor = conn.cursor()
 sql = cursor.execute("INSERT INTO posts(title, content, price, product_id) VALUES(?,?,?,?)",
 (yourtitleTextField.text(),
  yourContentTextField.text(), 
  yourPriceTextField.text(), 
  yourProductIDtextfield.text() )
  )

conn.commit()