2/2 pymysql：基础操作总结

操作	使用
删除TABLE	DROP TABLE IF EXISTS tb_name
创建TABLE	CREATE TABLE [IF NOT EXISTS] tb_name(col_name data_type,…)
更新COLUMN	UPDATE tb_name SET col_name = col_name+1 WHERE col_name = ‘%s’" %(‘M’)
插入记录	INSERT tb_name [(col_name,…)] VALUES(val,…)
查找记录	SELECT * FROM EMPLOYEE WHERE col_name>%s" %(s)
删除记录	DELETE FROM tb_name WHERE col_name >%s" %(20)

• 创建TABLE

>>> import pymysql

# 打开数据库连接
>>> db = pymysql.connect(host='localhost',user='root',passwd='20171018',db='TESTDB',charset='utf8')

# 使用cursor()方法获取操作游标 
>>> flag = db.cursor()

# 使用execute()方法执行SQL语句
>>> flag.execute('DROP TABLE IF EXISTS EMPLOYEE')  # 如果数据表已存在，使用execute()方法删除表

# 创建数据表SQL语句
>>> sql = """CREATE TABLE EMPLOYEE(
... FIRST_NAME CHAR(20) NOT NULL,
... LAST_NAME CHAR(20),
... AGE INT,
... SEX CHAR(1),
... INCOME FLOAT)"""
>>> flag.execute(sql)

# 关闭数据库连接
>>> db.close()

• 更新COLUMN

>>> import pymysql
>>> db = pymysql.connect(host='localhost', user='root', passwd='20171018', db='TESTDB', charset='utf8')
>>> flag = db.cursor()

>>> sql = "UPDATE EMPLOYEE SET AGE = AGE+1 \
... WHERE SEX = '%s'" %('M')

>>> try:
...     flag.execute(sql)
...     db.commit()
... except:
...     db.rollback()
...
3

• 插入记录

>>> import pymysql
>>> db = pymysql.connect(host='localhost', user='root', passwd='20171018', db='TESTDB', charset='utf8')

>>> flag = db.cursor()
>>> sql = """INSERT EMPLOYEE VALUES(
... 'hahaha', 'ha', 22, 'F', 2222)"""

>>> try:
...     flag.execute(sql)     # 执行sql语句
...     db.commit()   # 提交到数据库执行
... except:
...     db.rollback()     # 发生错误时回滚
...
1

# 关闭数据库连接
>>> db.close()

• 查询记录

Python查询Mysql使用fetchone()方法获取单条数据, 使用fetchall()方法获取多条数据。

方法	描述
fetchone()	获取下一个查询结果集，结果集是一个对象。
fetchall()	接收全部的返回结果行。
rowcount	这是一个只读属性，并返回执行execute()方法后影响的行数。

>>> import pymysql
>>> db = pymysql.connect(host='localhost', user='root', passwd='20171018', db='TESTDB', charset='utf8')

>>> flag = db.cursor()
>>> sql = "SELECT * FROM EMPLOYEE WHERE INCOME>%s" %(1000)
>>> try:
...     flag.execute(sql)
...     results = flag.fetchall()  # 获取所有记录列表
...     for row in results:
...             fname = row[0]
...             lname = row[1]
...             age = row[2]
...             sex = row[3]
...             income = row[4]
...             print("fname=%s, lname=%s, age=%s, sex=%s, income=%s"%(fname,lna
me,age,sex,income))
... except:
...     print('Error')
...
3
fname=Mac, lname=Mohan, age=20, sex=M, income=2000.0
fname=Test, lname=test, age=20, sex=M, income=2000.0
fname=hahaha, lname=ha, age=22, sex=F, income=2222.0

>>> db.close()

• 删除记录

>>> import pymysql
>>> db = pymysql.connect(host='localhost', user='root', passwd='20171018', db='TESTDB', charset='utf8')
>>> flag = db.cursor()

>>> sql = "DELETE FROM EMPLOYEE WHERE AGE >%s" %(20)
>>> try:
...     flag.execute(sql)
...     db.commit()
... except:
...     db.rollback()
...
3

>>> flag.execute("SELECT * FROM EMPLOYEE")
1
>>> results = flag.fetchall()
>>> for i in results:
...     print(i)
...
('nonono', 'no', 2, 'M', 50.0)