Python and MySQL

First import the python module containing the API



In [1]:

    
import pymysql









    



---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-1-a4103d9b2333> in <module>()
----> 1 import pymysql

ImportError: No module named 'pymysql'

Set up a connection and create a cursor object



In [14]:

    
db = pymysql.connect("localhost","root","None" ,database="schooldb")
cursor = db.cursor()

Execute a query and get the results



In [16]:

    
cursor.execute('show tables;')
cursor.fetchall()









    Out[16]:





(('Course',), ('Enrolls_in',), ('Student',))



In [21]:

    
query = """
SELECT course.name FROM student 
INNER JOIN enrolls_in ON student.ssn = enrolls_in.ssn 
INNER JOIN course ON course.number = enrolls_in.class
WHERE f_name = "JOHN";
"""
cursor.execute(query)
cursor.fetchall()









    Out[21]:





(('Data analytics',), ('Python',))



In [ ]:

    
# 向数据库插入数据
insert = 'INSERT INTO Student VALUES ("", "")' 
cursor.execute(insert)
cursor.fetchall()



In [ ]:

    
# 同步到数据库
db.commit()