Detailed explanation of DB-API for Python connection to database learning

Before the Python DB-API, the application interfaces between databases were very confusing and the implementations were different. If the project needs to replace the database, it will require a lot of modifications, which is very inconvenient. The emergence of Python DB-API is to solve such problems. This article mainly introduces the relevant information of DB-API for Python to connect to the database. Friends in need can refer to it.

Preface

Everyone knows that if you want to connect to a database in Python, whether it is MySQL, SQL Server, PostgreSQL or SQLite, use Cursors are always used, so you have to learn Python DB-API.

All Python database interface programs comply with the Python DB-API specification to a certain extent. DB-API defines a series of necessary objects and database access methods to provide consistent access interfaces for various underlying database systems and various database interface programs. Since DB-API provides a consistent access interface for different databases, porting code between different databases becomes an easy task.

Python connection database process:

##Create using connect connection connection

#The connect method generates a connect object through which we access the database. Modules that comply with the standard will implement the connect method.

The parameters of the connect function are as follows:

• user Username

• password Password

• host Hostname

• database Database name

• dsn Data source name

  
  

Database connection parameters can be provided in the form of a DSN string, example: connect(dsn='host:MYDB',user='root',password=' ')

Of course, different database interface programs may have some differences, and not all are implemented strictly in accordance with the specifications. For example, MySQLdb uses the db parameter instead of the database parameter recommended by the specification to indicate the database to be accessed:

Parameters available when connecting to MySQLdb

• host: database host name. The default is the local host

• user: database Login name. The default is the current user

• passwd: The secret of database login. The default is empty

• db: The database name to be used. None Default value

• port: TCP port used by MySQL service. The default is 3306

• charset: Database encoding

  
  

Parameters available when connecting to psycopg2:

##dbname – database name (dsn connection mode)
database – database name
user – username
password – password
host – Server address (if the default connection Unix Socket is not provided)
port – Connection port (default 5432)

• 
  

The connect object has the following methods:

close(): Close this connect object. After closing, no further operations can be performed unless the connection is created again
commit(): Submit the current transaction. If the database supports transactions and there is no commit after adding, deleting or modifying, the database will rollback by default

• ##rollback( ): Cancel the current transaction

• cursor(): Create a cursor object

• 
  

Use cursor to create a cursor object

cursor cursor object has the following properties and methods:

Common methods:

close(): Close this cursor object

• fetchone(): Get the next row of the result set

• fetchmany([size = cursor.arraysize]): Get the next few rows of the result set

• fetchall(): Get all the remaining rows of the result set

• excute(sql[, args]): Execute a database query or command

• excutemany(sql, args):Execute multiple database queries or commands
Common properties:

connection: Create a database connection for this cursor object

• arraysize: How many records are fetched at one time using the fetchmany() method, the default is 1

• lastrowid: Equivalent to PHP's last_inset_id()

• 
  

Other methods:

##__iter__(): Create an iterable object (optional)

• next(): Get the next row of the result set (if iteration is supported)

• nextset(): Move to the next result set (if it is supported)

• callproc(func[,args]): Call a stored procedure

• setinputsizes(sizes): Set the maximum input value (must exist, but the specific implementation is Optional)

• setoutputsizes(sizes[,col]): Set the maximum buffer size for large column fetch
  

##Other properties:

• description: Returns the cursor activity status (tuple containing 7 elements): (name, type_code, display_size, internal_size, precision, scale, null_ok) only name and type_cose is required

• rowcount: The number of rows created or affected by the most recent execute()

• messages: The information returned by the database after the cursor is executed Tuple (optional)

• rownumber: The index of the row where the cursor is located in the current result set (the starting row number is 0)

Error definition in DB-API only

Hierarchical relationship of error classes:

Database operation example

The code is as follows:

For more related articles on DB-API for Python connection to database learning, please pay attention to the PHP Chinese website!