方式一 直接连接 #!/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操作数据库

标签：获取   status   执行sql   关闭数据库   cep   html   检查   打印   elf