Python SQLserver database operations

SQL Server

SQL Server is a relational database management system developed and promoted by Microsoft.

SQL Server Features

1. C/S architecture
2. Has good scalability
3. Provide data warehouse functionality
4. Supports OLE DB and multiple queries

Code implementation of SQL server creation, insertion, query, and deletion operations

requires support from the pymsql library.

import pymssql
import time
import traceback, sys
class database(object):
"""database operation object"""
def __init__(self, url,username,password,databaseName):
  self.url = url
  self.username = username
  self.password = password
  self.databaseName = databaseName
  self.connect = self.sql_server_conn()
  self.cursor = self.connect.cursor()
  
def sql_server_conn(self):
  connect = pymssql.connect(self.url,self.username,self.password,self.databaseName) #server name, account, password, database name 
  if connect:
      print(u"Success!!")
  return connect
#view all fields in the table, 
#@table_name : table name 
def get_column_name(self,table_name):
  self.cursor.execute("select top 1 * from " + table_name)   #implement sql statement 
  data_dict=[]
  for field in self.cursor.description:
      data_dict.append(field[0])
  print(data_dict)
  return data_dict
#obtain all table names in the database 
def get_table_name(self):
  sql = "SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' ORDER BY NAME"
  self.cursor.execute(sql)  #return the number of successful execution results 
  rows = self.cursor.fetchall()
  for d in rows:
      for k in d:
          print(k)
          
#implement sql statement, adding, deleting, modifying, and checking 
#@sql:sql statement 
def execute_sql(self,sql):
  sql = sql.lower()
  if 'insert' in sql or 'delete' in sql or  'update' in sql:
      self.cursor.execute(sql)
      self.connect.commit()
      return 
  elif 'select' in sql :
      self.cursor.execute(sql)
      rows = self.cursor.fetchall()
      for k  in rows:
          print(k) 
      return rows
#close cursor, connect 
def close(self):
  self.cursor.close()   #close cursor 
  self.connect.close()