Python and database [1]-Database interface/DB-API-SQL Server Adapter

Sql_server Adapter /Sql_server Adapter

1 Environment Configuration /Environment Configuration

Installing the Sql_server Python Adapter pack

Pip Install pymssql  

Note: The remote database server is used here, so the local sql_server is not installed

2 Sql_server Example /Sql_server Examples

Because the remote database account has only Read permissions, the sample here is basically read-only.

View Database /Show Database

Log in to the remote database server with the basic functions of the adapter, and view information such as the database name, table name, and so on in the server.

1 ImportPymssql as Ms2 defExecute (cur, SQL, *args):3Cur.execute (SQL, *args)4Re =Cur.fetchall ()5     returnRe6 7 defShow ():8CNX = Ms.connect (host=' Host', user=' User', password='Password', database=' db', charset='UTF-8')9     TenCur =cnx.cursor () One      A     Print('{:-^30}'. Format ('DATABASES')) -     #Show the database in sys -Re = execute (cur,"SELECT * from sys.databases") the      -     #Show Custom Databases -     #re = execute (cur, "select * from sys.databases" WHERE name is not in (' Master ', ' tempdb ', ' model ', ' msdb ') ") -      forDbinchRe: +         Try: -             #Select Databases +Cur.execute ("Use %s"%db[0]) A         except: at             Continue -         #Show Tables -Re = execute (cur,"SELECT * from INFORMATION_SCHEMA. TABLES") -      -          forIinchRe: -             Print(i) in      -Cur.execute ('Use ITP') to     Print('{:-^30}'. Format ('TABLES')) +Re = execute (cur,"SELECT * from INFORMATION_SCHEMA. TABLES") -      forTbinchRe: the         Print('\n{:-^30}'. Format ('TABLE CONTENTS')) *         Print(TB) $         Print('{:-^30}'. Format ('%s'% tb[2]))Panax NotoginsengRe = execute (cur,"SELECT * from INFORMATION_SCHEMA. COLUMNS WHERE table_name= '%s '"% tb[2]) -          forClinchRe: the             Print(CL) +  A defFoo (gen): the[Print(X, end='\ n') forXinchGen] +  - if __name__=='__main__': $Show ()

第2-4 line, first define an execution function to execute the SQL statement with the incoming cursor

第7-39, define a display function where the name information of all databases is stored in sys.databases, and the show function will display all database information and view the information of the DB table

The database information is hidden here, it can be seen, whether it is sql_server or MySQL, using the adapter to operate, the rationale is the same, only some of the SQL statements are different.

Related reading

1. Using Python to build a MySQL adapter

2. Db-api General Standard

Python and database [1]-Database interface/DB-API-SQL Server Adapter