连接mysql数据库
查询mysql版本信息
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
# Author: EricRay
# Created Time : 2017-12-27 16:45:02
# File Name: db.py
# Description:查询Mysql版本信息
"""
import pymysql
#打开数据库连接
db = pymysql.connect("192.168.122.58","eric","eric2017","test")
#pymysql.connect("localhost/IP","username","pwd","databs" )
#使用cursor()方法创建一个游标对象 cursor
cursor = db.cursor()
#使用execute()方法执行sql查询
cursor.execute("select VERSION()")
#使用 fetchone()方法获取单条数据
data = cursor.fetchone()
print ("database version : %s" % data)
#关闭连接
db.close()
建表
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
# Author: EricRay
# Created Time : 2017-12-27 18:31:13
# File Name: cr_t.py
# Description:
"""
import pymysql
#打开数据库连接
db = pymysql.connect("192.168.122.58","eric","lyd2017","test")
cursor = db.cursor()
cursor.execute("drop table if exists employee")
#预处理语句建表
sql = """create table employee(
tname varchar(20) not null default '',
age int,
sex varchar(1)) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
cursor.execute(sql)
db.close()
插入数据
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
# Author: EricRay
# Created Time : 2017-12-27 18:38:56
# File Name: insert.py
# Description:
"""
#!/usr/bin/python3
import pymysql
# 打开数据库连接
db = pymysql.connect("192.168.122.58","eric","lyd2017","test" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
sql = "insert into employee(tname,age,sex) \
values('%s','%d','%s')" % \
('Mary',18,'F')
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
查询
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
-
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
-
fetchall(): 接收全部的返回结果行.
-
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
# Author: EricRay
# Created Time : 2017-12-27 19:07:30
# File Name: select.py
# Description:
"""
import pymysql
# 打开数据库连接
db = pymysql.connect("192.168.122.58","eric","lyd2017","test" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
#查询
sql = "select * from employee"
try:
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
tname = row[0]
age = row[1]
sex = row[2]
#打印结果
print('tname = %s, age = %d, sex = %s' % \
(tname,age,sex))
except:
print("Error: unable to fetch data")
db.close()
结果
(my_env3.6)
# eric @ ray in ~/.local/virtualenvs/my_env3.6/code [19:14:05]
$ python select.py
tname = eric, age = 28, sex = M
tname = Mary, age = 18, sex = F
