操作使用
| 删除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) |
>>> 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()
>>> 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)
总结
以上是生活随笔为你收集整理的2/2 pymysql：基础操作总结的全部内容，希望文章能够帮你解决所遇到的问题。
如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。
