database objects for Python

Install the Python driver for MySQL and Sqlserve:

Install Pymysql: python3-m pip Install Pymysql, view the installed version python3-m pip show Pymysql.

　　

Install pymssql:python3-m pip install pymssql to view the installed version python3-m pip show pymssql.　

　　

as a result of the lack of-mssql, the reference to the solution http://blog.csdn.net/HHTNAN/article/details/77931782, we use the second solution. WHL for https:// www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql, load command for python3-m PIP

Install PYMSSQL-2.1.3-CP36-CP36M-WIN32.WHL. View the installed version python3-m pip show pymssql.

　　

Database connection operation flow and function Description:

any database can not be avoided is the connection operation, here MySQL and SQL Server connection transfer parameters slightly different,　　 pymssql.connect (self.host,self.user,self.pwd,self.db,charset= "UTF8"), SQL Server is passed 4 parameters, host IP plus port, username, password, library name , character encoding; and MySQL's pymysql.connect (host=self.host,port=self.port,user=self.user,passwd=self.pwd,db=self.db, charset= "UTF8") the other is the same, host for IP plus a separate port for oral arguments.

The query statement creates a cursor object CUR by conn The cursor of the object returned after the connection, invokes Cur's Execute method to execute the SQL statement, queries the single statement with the Fetchone, and Fetchall queries for multiple pieces of data. The Close method of Conn is then called to close the connection.

    non-query statement    , Create a Cursor object CUR by using the cursor method of the object returned after the connection is Conn,   calls the Execute method of the CUR execute the SQL statement, and then Conn calls the Commit method to commit the data, and then calls close the method of closing the database connection. 
  Create database objects and test examples:     

#-*-coding:utf-8-*-s#mysql and SQL Server library import pymysql,pymssqlclass database:def __init__ (self,*db): If len (db) = = 5: #mysql数据库 self.host=db[0] self.port=db[1] self.user=db[2] self.p WD=DB[3] self.db=db[4] Else: #sqlserver数据库 self.host=db[0] Self.port=no NE self.user=db[1] self.pwd=db[2] self.db=db[3] def _getconnect (self): if not s Elf.db:raise nameerror+ "Database information not set" if (Self.port==none): Self.conn=pymssql.connect (self.host , self.user,self.pwd,self.db,charset= "UTF8") Else:self.conn = Pymysql.connect (host=self.host,port=self.p            ort,user=self.user,passwd=self.pwd,db=self.db,charset= "UTF8") cur=self.conn.cursor () If not cur:        Raise "database connection not on" Else:return cur #查询sql def execquery (self,sql): Cur=self._getconnect ()    Cur.execute (SQL)    Relist=cur.fetchall () Self.conn.close () return relist #非查询的sql def execnoquery (self,sql): C Ur=self._getconnect () cur.execute (SQL) Self.conn.commit () Self.conn.close ()

Test code:

#sqlserver数据库信息SqlServerhost = "192.168.100.85:12033" sqlserveruser= "Aktest" sqlserverpwd= "btjf123!" sqlserverdb= "Ak_data_jccs" #Mysql数据库信息Mysqlhost = "192.168.100.211" mysqlport=3307mysqluser= "Akmysql" mysqlpwd= " Mysql123 "mysqldb=" Bt_hyaline "Database=database (SQLSERVERHOST,SQLSERVERUSER,SQLSERVERPWD,SQLSERVERDB) # SQL Server Query sql= "SELECT top 1 * from T_code ORDER BY fid DESC" relist=database. ExecQuery (SQL) print (relist) #sqlserver非查询sql = "Update t_code set Ftypes=1 where fid=83902" database. Execnoquery (SQL) database=database (mysqlhost,mysqlport,mysqluser,mysqlpwd,mysqldb) #Mysql查询sql = "SELECT * FROM T_ Bank WHERE FID = 2 "relist=database. ExecQuery (SQL) print (relist) #Mysql非查询sql = "Update T_bank set fshortname=2 where FID = 2" database. Execnoquery (SQL)

　　

database objects for Python