connection python and SQL

[Pedroski55]

I think it is a good idea to access the database using phpMyAdmin first. Make an SQL query and try it, to make sure that it works. When you know you have a query that works, then you can do it from Python.

I only do simple stuff. For me, pymysql works fine.

import pymysql 

def mysqlINSERT(name, email, comment): 
    conn = pymysql.connect( 
        host='127.0.0.1', 
        user='baby',  
        password = 'Taizhou', 
        db='babydb', 
        )        
    cur = conn.cursor() 
    # INSERT query 
    command = 'INSERT INTO agro_products (customer_id, product_name, product_class) VALUES (%s, %s, %s)'
    cur.execute(command, (name, email, comment))
    # without conn.commit() all you get is simulation, nothing is actually written
    conn.commit()
    # To close the connection 
    conn.close()
    return 'OK'

name = 'Use me'
email = '[email protected]'
comment = 'Hope this kills everything!'
doit = mysqlINSERT(name, email, comment)

Here is a query to return the column names of a given table:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'babydb' AND TABLE_NAME = 'agro_products';

Try it in phpMyAdmin first, using your database name and table name, of course. When you know it works, try it in Python.