Python notes (12): Manipulating Databases

(i) Preface

This article explains how to connect to Oracle, MySQL, SQL Server, and execute SQL, get query results, and more.

(ii) Db-api

Db-api clarifies the criteria for a range of required objects and database access mechanisms.

Python Operations Database Module if you follow DB-API standard (should follow this standard), function, method name and function should be similar (the following table lists some of the content), the parameters may be a little different.

Connect function

The Connect function accesses the database through the Connection object. The function creates and returns a Connection object.

parameters	Description
Host	Access address of the DB instance (example: ip\ instance name)
User	Username
Password	Password
Database	Database name

                 /                                   ,         &NB Sp  connection Object      

method	Description
Close ()	Close database connection
commit ()	Commit TRANSACTION
Rollback ()	Cancel transaction
Cursor ()	Use this connection to create and return a cursor object (or a class cursor object)

cursor Object

(feel more important in the following 3)

Method	Description
Execute ()	Execute SQL
Fetchall ()	Get query results (after executing SELECT statement)
Callproc ()	Call a stored procedure

Abnormal

Abnormal	Description
DataError	There was a problem working with the data
Operationalerror	A problem occurred during database operation execution
Integrityerror	Database Relationship Integrity Error
Programmingerror	SQL execution failed

(iii) Module Installation

Mysql:pip Install Pymysql

SQL server:https://www.lfd.uci.edu/~gohlke/pythonlibs/Download pymssql, and then pip install XXX.WHL. (Pip install pymssql can not be installed in this way, the above 2 if not installed in this way to install)

(iv) Oracle Example

As you can see from the following 3 examples, the code is essentially the same, except that the imported modules are not the same (other databases should be similar).

Importcx_oracle#connection database, Parameters: Username/password @ server IP: Port number/instance nameconn = Cx_oracle.connect ('Py/[email PROTECTED]:1521/ORCL')#creating a Cursor objectCur =conn.cursor ()#Execute SQLCur.execute ("INSERT into Test_py t values (' 1005 ', ' ZS ', ' Zhang San ', ' 123456 ')") Cur.execute ("update test_py t set t.user_name= ' John Doe ' where t.id= ' 123 '")#Commit a transactionConn.commit ()#Execute SQLCur.execute ("SELECT * from Test_py")#Get query Resultsrow =Cur.fetchall ()Print(Row)#To close a database connectionConn.close ()

(v) MYSQL Example

1 ImportPymysql2 3 #Connect to database, host: Server IP user: username password: password database: DB name4conn = Pymysql.connect (host='192.168.4.196', user='Root', password='Password', database='Test')5 #creating a Cursor object6Cur =conn.cursor ()7 #Execute SQL8Cur.execute ("INSERT INTO test_py (Id,user_account) VALUES (' + ', ' admin ')")9Cur.execute ("Update test_py Set user_account = ' Test6 ' where id= ' 123 '")Ten #Commit a transaction One Conn.commit () A #Execute SQL -Cur.execute ("SELECT * from Test_py") - #Get query Results therow =Cur.fetchall () - Print(Row) - #To close a database connection -Conn.close ()

(vi) SQL Server Example

Importpymssql#Connection Database, Host: Server ip\\ instance name User: username password: password database: DB nameconn = Pymssql.connect (host='192.168.4.196\\amsys', user='SA', password='123', database='Test')#creating a Cursor objectCur =conn.cursor ()#Execute SQLCur.execute ("INSERT INTO test_py (Id,user_account) VALUES (' 100861 ', ' admin ')") Cur.execute ("Update test_py Set user_account = ' Test6 ' where id= ' 123 '")#Commit a transactionConn.commit ()#Execute SQLCur.execute ("SELECT * from Test_py")#Get query Resultsrow =Cur.fetchall ()Print(Row)#To close a database connectionConn.close ()

Python notes (12): Manipulating Databases