Python操作数据库

#方式一 直接连接
#!/usr/bin/python3
# a sample to use mysql-connector for python3
# see details from   http://dev.mysql.com/doc/connector-python/en/index.html
 
import pymysql
 
 
#设置SQL
sql = SELECT mobile,email,cust_short_name,cust_id,create_time FROM db_cust.t_cust_info WHERE cust_id IN ("CB0000031509","CP0000003520")AND institution_type = "COMMON" AND record_status = 1 AND `status` = "ENABLE" ORDER BY ID ASC;
 
 
def mysql_test():
    #创建数据库连接
    db = pymysql.connect(host=192.168.3.15,
                         port=3306,
                         user=tester,
                         passwd=Aa123456,
                         database=db_cust,
                         )
    cursors = db.cursor()
    # 执行SQL
    cursors.execute(sql)
 
    #接收查询的数据
    info = cursors.fetchall()
 
    #打印查询的二维数组数据
    for i in range(len(info)):
        for j in range(len(info[0])):
            print(info[i][j])
 
    #关闭连接
    cursors.close()
    db.close()
 
 
if __name__ == "__main__":
    mysql_test()
 
#方式二 跳板机SSH连接
 
import pymysql
from sshtunnel import SSHTunnelForwarder
 
 
 
# 传入实例名和sql,返回查询结果
def SSHMysql(DB, SQL):
    # 配置SSH连接
    server = SSHTunnelForwarder(
        ssh_address_or_host=(140.130.74.54, 4888),  # 指定ssh登录的跳转机的address
        ssh_username=***,  # 跳转机的用户
        ssh_password=***,  # 跳转机的密码
        local_bind_address=(127.0.0.1, 1268),  # 映射到本机的地址和端口
        remote_bind_address=(16.1.24.201, 61113))  # 数据库的地址和端口
    server.start()  # 启用SSH
    # 数据库账户信息设置
    db = pymysql.connect(
        host="127.0.0.1",  # 映射地址local_bind_address IP
        port=1268,  # 映射地址local_bind_address端口
        user="*****",
        passwd="*****",
        database=db_cust,  # 需要连接的实例名
        charset=utf8)
 
    cursor = db.cursor()
    cursor.execute(SQL.encode(utf8))  # 执行SQL
    data = cursor.fetchall()  # 获取查询结果
 
    # 关闭数据库连接
    cursor.close()
    return data
 
 
if __name__ == "__main__":
   SQL="SELECT * FROM t_cust_batch;"
   SelectResult = SSHMysql(db_cust, SQL)

 

Python操作数据库,装饰器管理数据库的的打开和关闭。

import pymysql
class ConDb():
    def openClose(fun):
        def run(self,sql=None):
            #创建数据库连接
            db=pymysql.connect(host=localhost,port=3306 ,user=root,password=root,db=ljj,charset=utf8)
            #创建游标
            cursor = db.cursor()
            try:
                #运行sql语句
                cursor.execute(fun(self,sql))
                #得到返回值
                li=cursor.fetchall()
                #提交事务
                db.commit()
            except Exception as e:
                #如果出现错误,回滚事务
                db.rollback()
                #打印报错信息
                print(运行,str(fun),方法时出现错误,错误代码:,e)
            finally:
                #关闭游标和数据库连接
                cursor.close()
                db.close()
            try:
                #返回sql执行信息
                return li
            except:
                print(没有得到返回值,请检查代码,该信息出现在ConDb类中的装饰器方法)
        return run
 
 
    @openClose
    def runSql(self,sql=None):
        if sql is None:
            sql=select * from batch
        return sql
 
    @openClose
    def runSql1(self,sql=None):
        return sql

 

Python操作数据库

上一篇:python-python与mysql交互(pymysql)


下一篇:Centos7安装Telnet服务