python3数据库配置,远程连接mysql服务器
发布时间
阅读量：
阅读量
python配置数据库mysql
- 安装mysql数据库，方法如下
- 安装pymysql
#在python虚拟环境中安装，我采用的是这种方法
$ pip install PyMySQL
Collecting PyMySQL
Downloading PyMySQL-0.8.0-py2.py3-none-any.whl (83kB)
100% |████████████████████████████████| 92kB 222kB/s
Installing collected packages: PyMySQL
Successfully installed PyMySQL-0.8.0
#PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
#PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
#安装成功
# eric @ ray in ~/.local/virtualenvs/my_env3.6/code [18:54:30]
$ python
Python 3.6.3 (default, Oct 6 2017, 08:44:35)
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymysql#输入不报错
>>>
- 连接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
全部评论 (0)
还没有任何评论哟~