武沛齐博客mysql,使用Python 操作 MySQL 数据库

插入

from __future__ import print_function

from datetime import date, datetime, timedelta

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')

cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)

add_employee = ("INSERT INTO employees "

"(first_name, last_name, hire_date, gender, birth_date) "

"VALUES (%s, %s, %s, %s, %s)")

add_salary = ("INSERT INTO salaries "

"(emp_no, salary, from_date, to_date) "

"VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# Insert new employee

cursor.execute(add_employee, data_employee)

emp_no = cursor.lastrowid

# Insert salary information

data_salary = {

'emp_no': emp_no,

'salary': 50000,

'from_date': tomorrow,

'to_date': date(9999, 1, 1),

}

cursor.execute(add_salary, data_salary)

# Make sure data is committed to the database

cnx.commit()

cursor.close()

cnx.close()

值得注意的是 commit() ，就是做数据库更新或插入操作后，一定要提交，否则是不会真正更新数据库的。

查询

import datetime

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')

cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "

"WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)

hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:

print("{}, {} was hired on {:%d %b %Y}".format(

last_name, first_name, hire_date))

cursor.close()

cnx.close()

这个是MySQL官方中的示例，但有一个问题，就是它是直接使用游标进行遍历。如果想在遍历过程中使用同样的链接做其它mysql操作，就会出现问题，因此如果数据集不大的时候，可以直接用 myresult = mycursor.fetchall() 的方法想将结果集全部取回来，这样游标就不会占用了。