python 3データベース構成、mysqlサーバへのリモート接続
python構成データベースmysql mysqlデータベースをインストールします.mysqlデータベースサーバ をインストールして構成します. pymysql をインストール接続mysqlデータベースクエリーmysqlバージョン情報 fetchone():このメソッドは、次のクエリー結果セットを取得します.結果セットはオブジェクト です. fetchall():すべての戻り結果行を受信. rowcount:これは読み取り専用プロパティであり、execute()メソッドを実行した後に影響するロー数を返します.
# 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#
>>>
#!/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()
#!/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