best way for python to interface with MySQL

First install mysqlclient as this will also provide you support to work with Python 3.6

pip install mysqlclient

Example code

    import mysql.connector
    import _mysql
    db=_mysql.connect("127.0.0.1","root","umer","sys")
    #db=_mysql.connect(host,user,password,db)
    # Example of how to insert new values:
    db.query("""INSERT INTO table1 VALUES ('01', 'myname')""")
    db.store_result()
    db.query("SELECT * FROM new1.table1 ;") 
    #new1 is scheme table1 is table mysql 
    res= db.store_result()
    for i in range(res.num_rows()):
        print(result.fetch_row())

pip install mysql failed for me on MacOS Sierra with EnvironmentError: mysql_config not found.

pip install pymysql works. PyMySQL is written in pure Python, so should be more cross-platform than statically compiled modules.

Example code:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('[email protected]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

Python connect xampp mysql windows with pymysql:

Step-1:

Run Command Prompt D:\python-3.6.2\Scripts> pip install pymysql

Step-2:

#!D:/python-3.6.2/python.exe 
print ("Content-Type: text/html\n")
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='', db='python_mysql')
cur = conn.cursor()
cur.execute("SELECT * FROM authors")
print(cur.description)
print(" Test")
for row in cur:
    print(row)
cur.close()
conn.close()

Save the file as testconn.py in htdocs/PythonProject & open http://localhost/PythonProject\testconn.py

Crud Python & MySQL:

https://github.com/matinict/python-in-xampp-for-windows/

import mysql.connector as connector


class DbHelper:
    def __init__(self):
        self.conn = connector.connect(host = '127.0.0.1', user = 'root', password = '', database = 'python_test')
##        print("Database connected...")

        query = 'create table if not exists user(userID int primary key, firstname varchar(200),lastname varchar(200), Dob varchar(15), contact varchar(10), address varchar(100))'
        curr = self.conn.cursor()
        curr.execute(query)
##        print("Table Created")
        print()


    def insert_Data(self, userID, firstname, lastname, Dob, contact, address):

        query = "insert into user (userID, firstname, lastname, Dob, contact, address) values({},'{}','{}','{}','{}','{}')".format(userID,firstname, lastname, Dob, contact, address)
            
        curr = self.conn.cursor()
        curr.execute(query)
        self.conn.commit()
        print(query)
        print("User data inserted....")

    def display_Data(self):
        query = 'select * from user'
##        print("query: ", query)
        print()

        curr = self.conn.cursor()
        curr.execute(query)

        for row in curr:
            print('userID :', row[0])
            print('firstname :', row[1])
            print('lastname :', row[2])
            print('Dob :', row[3])
            print('contact :', row[4])
            print('address :', row[5])
            print()
            print()
            
##        print(" All user data....")
        

    def delete_user(self, userID):

        query = "DELETE FROM user WHERE userID={}".format(userID)
        curr = self.conn.cursor()
        curr.execute(query)
        self.conn.commit()
##        print(query)
        print("Data delete for UserID " +str(userID))

    def update_Data(self, userID, newfirstname, newlastname, newDob, newcontact, newaddress):

        query = "update user set firstname = '{}', lastname ='{}', Dob = '{}', contact = '{}', address = '{}' where userID = {}".format(newfirstname, newlastname, newDob, newcontact, newaddress,userID)
        curr = self.conn.cursor()
        curr.execute(query)
        self.conn.commit()
##        print(query)
        print()
        print("Data updated for UserID " +str(userID))
           
        

##db = DbHelper()
##db.update_Data(1,'saurabh','sharma','25-06-1998','9892809732','malad')