Python3操作MySQL数据库

1. 使用PyMySQL连接MySQL数据库

1.1 准备工作

1. 安装PyMySQL，使用pip安装即可

    pip install PyMySQL

2. MySQL数据准备（过程略，结果如下图）

   

1.2 查询下一条结果

代码示例：

import pymysql

# 打开连接
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='star', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 执行SQL
sql = 'select * from star'
cursor.execute(sql)
# 获取全部数据
data = cursor.fetchone()
print('当前数据: ', data)
data_1 = cursor.fetchone()
print('下一条数据: ', data_1)
# 关闭数据库连接
conn.close()

执行结果：

当前数据:  (1, '杨幂', datetime.date(1984, 6, 1))
下一条数据:  (2, '李小璐', datetime.date(1989, 5, 1))

1.3 查询全部结果

代码示例：

import pymysql

# 打开连接
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='star', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 执行SQL
sql = 'select * from star'
cursor.execute(sql)
# 获取全部数据
data = cursor.fetchall()
print('全部数据: ', data)
print('第一条数据: ', data[0])
# 按行打印查询结果（可选字段）
for row in data:
    print(row[1], row[2])
# 关闭数据库连接
conn.close()

执行结果：
全部数据:  ((1, '杨幂', datetime.date(1984, 6, 1)), (2, '李小璐', datetime.date(1989, 5, 1)), (3, '赵奕欢', datetime.date(1990, 10, 10)), (4, '谭松韵', datetime.date(1993, 7, 1)), (5, '孙允珠', datetime.date(2001, 3, 8)))
第一条数据:  (1, '杨幂', datetime.date(1984, 6, 1))
杨幂 1984-06-01
李小璐 1989-05-01
赵奕欢 1990-10-10
谭松韵 1993-07-01
孙允珠 2001-03-08

1.4 参考资料

https://github.com/PyMySQL/PyMySQL

http://www.runoob.com/python3/python3-mysql.html

2. 使用Paramiko远程连接服务器并执行MySQL命令

2.1 准备工作

1. 安装Paramiko，使用pip安装即可

pip install paramiko

2.数据准备（略）

2.2 示例代码

注：使用时替换代码中的各地址、端口、账号、密码、数据库及SQL即可

import paramiko

# 打开连接
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# 连接服务器, 需要服务器地址, 端口, 登录用户, 登录密码
ssh.connect('123.45.67.89', 12345, 'root', '123456')
# 连接数据库并执行SQL, 需要数据库地址, 数据库账号及密码, 查询SQL
database = 'user'
sql = 'SELECT id, name, mobile FROM fy_agent WHERE mobile IN (13012345678, 13212345678)'
sql1 = 'mysql -h %s -u %s -p%s -D%s -e "%s"' % ('abc.mysql.rds.aliyuncs.com', 'root', '123456', database, sql)
stdin, stdout, stderr = ssh.exec_command(sql1)
# 打印结果
result = stdout.readlines()
print(result)
data_1 = result[1]
print(data_1)                # 查询结果默认带表头, 结果数据从下一条记录开始;
print(data_1.replace('\t', '').replace('\n', ''))    # 将结果中的\t\n替换掉
# 关闭连接
ssh.close()

执行结果：

['id\tname\tmobile\n', '13480\t红包昆明二\t13212345678\n', '13488\t广东\t13012345678\n']
13480 红包昆明二 13212345678

13480红包昆明二13212345678

2.3 参考资料

http://www.paramiko.org/

http://blog.csdn.net/songfreeman/article/details/50920767

3.使用sshtunnel远程连接服务器并执行MySQL命令

import pymysql
from sshtunnel import SSHTunnelForwarder


class DB:
    def __init__(self):
        self.server = SSHTunnelForwarder(('123.45.67.89', 10086), ssh_password='password', ssh_username='user', remote_bind_address=('rm-2abcdefghi.mysql.rds.aliyuncs.com', 3306), )
        self.server.start()
        self.conn = pymysql.connect(host='127.0.0.1', port=self.server.local_bind_port, user='tester', password='tester', db='user_info', charset='utf8')
        self.cursor = self.conn.cursor()

    def __del__(self):  # 析构函数，实例删除时触发
        self.cursor.close()
        self.conn.close()
        self.server.close()

    def query(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def exec(self, sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print(e)