El primer paso es instalar controlador de MYSQL mediante PIP:

pip install mysql-connector

Una vez que tengamos instalado mysql connector ya podemos realizar operaciones en MySQL.

Crear manualmente una base de datos

 1 $ mysql -u root -p 
 2 
 3    SHOW DATABASES;
 4 
 5    CREATE USER 'foobar'@'localhost' IDENTIFIED BY 'no secret';
 6    GRANT ALL PRIVILEGES ON fb_db . * TO 'foobar'@'localhost';
 7    GRANT ALL PRIVILEGES ON * . * TO 'foobar'@'%' IDENTIFIED BY 'no secret';
 8    FLUSH PRIVILEGES;
 9 
10    exit

Crear una tabla manualmente:

 1 $ mysql -u foobar -p 
 2 
 3   USE fb_db;
 4   CREATE TABLE person (
 5     id INTEGER PRIMARY KEY AUTO_INCREMENT,
 6     name VARCHAR(255),
 7     birthdate DATE,
 8     score REAL
 9   );
10 
11   INSERT INTO person (name, birthdate, score)
12       VALUES ("Foo Bar", "1998-05-23", 42.1)

Crear un conector a MySQL

 1 import mysql.connector
 2 
 3 def main():
 4     conn = mysql.connector.connect(
 5         host = 'localhost',
 6         database = 'fb_db',
 7         user = 'foobar',
 8         password='no secret')
 9 
10     print("Connected:", conn)
11 
12     conn.close()
13 
14 if __name__ == "__main__":
15     main()

Consultar tabla

 1 import mysql.connector
 2 
 3 
 4 def main():
 5     conn = mysql.connector.connect(
 6         host = 'localhost',
 7         database = 'fb_db',
 8         user = 'foobar',
 9         password='no secret')
10 
11     cursor = conn.cursor()
12     cursor.execute("SELECT * FROM person")
13 
14     while True:
15         row = cursor.fetchone()
16         if not row:
17             break
18         print(row)
19 
20     cursor.close()
21     conn.close()
22 
23 if __name__ == "__main__":
24     main()

Consultar tabla con parámetros:

 1 import mysql.connector
 2 
 3 
 4 def main(min_score):
 5     conn = mysql.connector.connect(
 6         host = 'localhost',
 7         database = 'fb_db',
 8         user = 'foobar',
 9         password='no secret')
10 
11     cursor = conn.cursor()
12     cursor.execute("SELECT * FROM person WHERE score > %s", (min_score,))
13 
14     size = 2
15 
16     while True:
17         rows = cursor.fetchmany(size)
18         if not rows:
19             break
20         print(len(rows))
21         for row in rows:
22             print(row)
23 
24     cursor.close()
25     conn.close()
26 
27 if __name__ == "__main__":
28     main(40)

Insertar datos

 1 import mysql.connector
 2 
 3 
 4 def main(name, birthdate, score):
 5     conn = mysql.connector.connect(
 6         host = 'localhost',
 7         database = 'fb_db',
 8         user = 'foobar',
 9         password='no secret')
10 
11     cursor = conn.cursor()
12     cursor.execute(
13         "INSERT INTO person (name, birthdate, score) VALUES (%s, %s, %s)",
14         (name, birthdate, score))
15 
16     if cursor.lastrowid:
17         print('last insert id', cursor.lastrowid)
18     else:
19         print('last insert id not found')
20     conn.commit()
21 
22     conn.close()
23 
24 if __name__ == "__main__":
25     main('Monty Python', '1969-10-05', 100)

Actualizar tabla

 1 import mysql.connector
 2 
 3 
 4 def main(uid, score):
 5     conn = mysql.connector.connect(
 6         host = 'localhost',
 7         database = 'fb_db',
 8         user = 'foobar',
 9         password='no secret')
10 
11     cursor = conn.cursor()
12     cursor.execute("UPDATE person SET score=%s WHERE id=%s",
13         (score, uid))
14     conn.commit()
15 
16     conn.close()
17 
18 if __name__ == "__main__":
19     main(12, 32)

Eliminar datos de la tabla

 1 import mysql.connector
 2 
 3 
 4 def main(uid):
 5     conn = mysql.connector.connect(
 6         host = 'localhost',
 7         database = 'fb_db',
 8         user = 'foobar',
 9         password='no secret')
10 
11     cursor = conn.cursor()
12     cursor.execute("DELETE FROM person WHERE id=%s", (uid,))
13     conn.commit()
14 
15     conn.close()
16 
17 if __name__ == "__main__":
18     main(11)

Conclusión.

Como puedes observar es sumamente sencillo realizar operaciones en MYSQL mediante Python. Recapitulando. Primero debes instalar la librería de conexión mediante pip install mysql-connector. Después debes conectarte a mysql usando las credenciales de tu instancia. Por último realizar operaciones como SELECT, UPDATE, DELETE para observar los cambios en tus registros.