Python + SQL Server Return result processing
How to build python + sql server Framework and use of database query result values .
config Under the folder sqlconfig.ini
[sqlserver]
host = 123.123.0.123
user = user-name
pwd = password
db = DB-name
config Under the folder sqlconfig.py
import configparser
import os
_ini_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'sqlconfig.ini').replace('\\', '/')
_conf = configparser.ConfigParser()
_conf.read(_ini_path, encoding='utf-8')
host = _conf.get('sqlserver', 'host')
user = _conf.get('sqlserver', 'user')
pwd = _conf.get('sqlserver', 'pwd')
db = _conf.get('sqlserver', 'db')
_conf.clear()
common Under the folder sqlclient.py
from configs.sqlconfig import *
import pymssql
import logging
class SqlClient:
def __init__(self, host=host, user=user, pwd=pwd, db=db):
self.host = host
self.user = user
self.pwd = pwd
self.db = db # Default connection VATVerification8020 database
def __getConnect(self):
""" establish SQL Connect """
try:
self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset="utf8")
cur = self.conn.cursor()
except pymssql.OperationalError:
pass
except Exception as e:
_logger.error('444:' % e)
else:
return cur
def query(self, sql):
"""
:param sql: Query statement
:return: Query results
"""
cur = self.__getConnect()
try:
cur.execute(sql)
resList = cur.fetchall()
self.conn.close()
_logger.info('000')
return resList
except AttributeError:
logging.error('1:%s' % 'connect sqlserver failed! SQL:{sql}'.format(sql=sql))
except pymssql.ProgrammingError:
logging.error('2:%s' % 'db error! SQL:{sql}'.format(sql=sql))
def executeSQL(self, sql):
"""
:param sql: Non query statements
:return: None
"""
cur = self.__getConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
_sqlClient = SqlClient()
if __name__ == "__main__":
result = _sqlClient.query("select name from user")
print(type(result))
print(result[1])
print(type(result[1]))
rr= list(str(x) for (x,) in result)
print(rr[1])
Execution results ：
From the results ,sql The result of the query is list, Each result value is a Tuples （tuple）, If you want to get a specific value, you can use list(str(x) for (x,) in result) Convert the returned results , hold list The elements of are converted into str, In forming a list. And then you can list Compare the data of … Wait for other operations .
