Mysql programming in Python

Python operations on databases may be of some use to me! I want to use it to flexibly import access data to mysql!
 
Import Module
Import MySQLdb
 
Database Operations
1. Connect to the database
 
Conn = MySQLdb. connection (host = 'host', user = 'user', passwd = 'passwd', db = 'db') conn = MySQLdb. connect (host = 'host', user = 'user', passwd = 'passwd', db = 'db ')
Both methods can return the connection object. The main parameters are as follows:
 
Host, the host where the database is located. The default value is 'localhost'
 
User: the username used to log on to the database. The default username is the current user.
 
Passwd: password used to log on to the database. The default value is null.
 
Db, the name of the opened database, no by default
 
Port: the port of the MySQL service. The default value is 3306.
 
 
2. transaction-related
 
# Commit and modify conn. commit () # transaction rollback conn. rollback ()
 
 
 
3. Get the cursor
 
Cursor = conn. cursor (cursorclass = MySQLdb. cursors. Cursor)
Cursorclass parameters:
 
MySQLdb. cursors. Cursor, default value. Execute the SQL statement to return the List. The data in each row is tuple.
 
MySQLdb. cursors. DictCursor: Execute the SQL statement to return the List. The data in each row is Dict.
 
 
4. perform the operation
 
Run the SQL statement 」:
 
Cursor.exe cute (SQL, params)
SQL: The executed SQL statement. % s is used for parameters.
 
Params: a common or tuple type. required parameters in SQL statements.
 
Returns the number of affected rows.
 
"Call a stored procedure 」:
 
Cursor. callproc (procname, args)
Procname: name of the stored procedure
 
Args, passed parameters
 
Returns the number of affected rows.
 
 
5. Accept the return value
 
# Return single row data result = cursor. fetchone () # return all data result = cursor. fetchall ()
As mentioned above, if MySQLdb. cursors. DictCursor is used to obtain the cursor, each row of data returned is of the Dict type. The key value of each pair is "field name: Data"
 
If multiple select statements are executed at the previous time, the sursor returns multiple result sets, and the cursor provides the corresponding method to move to the next result set.
 
Cursor. nextset ()
 
6. Close the connection
 
Develop good habits. Close the cursor object and database connection object in time when you are not using the database.
 
Cursor. close () conn. close ()
 
Copyright of The 0th Space