Connect to and operate MySQL databases using Python

• #! / Usr/ Bin/ Env Python #- * - Encoding: UTF- 8- * - "" " Test MySQL connection and operations " "" import MySQLdb Connstring = "Host = 'localhost', Port = 3306, user = 'root', passwd = '*****', DB = 'python '" # Connection string Try : Conn = Mysqldb. Connect ( Connstring) Except Exception , E: Print E Break Mycursor = Conn. Cursor ( ) # Obtain the cursor and use the cursor to operate the database # Creating a table Ctable = "" "Create table test if not exists (name varchar (30), uid int (10) Primary Key )" "" Mycursor. Execute ( Ctable) # Insert data Insert1 = "" "Insert into test (name = 'aaa', uid = 111 )" "" Insert2 = "" "Insert into test (name = 'bbb ', uid = 222 )" "" Insert3 = "" "Insert into test (name = 'ccc ', uid = 333 )" "" Inserts = [ ] Inserts [ 0] . Append ( Insert1) Inserts [ 1] . Append ( Insert2) Inserts [ 2] . Append ( Insert3) For Insert in Inserts: Try : Mycursor. Execute ( Insert) Except Exception , E: Print E # Deleting data # Comment out. Data is used for the following query and only operations are recorded. # Mycursor. Execute ( "" "Delete from test Where name = 'aaa '" "" ) # Delete multiple tables # Deltas = "" "" Delete from Table1, Table2, Table3 where table1. Uid = Xxx and table2. uid= table3. uid"" " #mycursor.execute(delmany)

  Continue ......

   

  # Querying a table Slct = "" "Select * from test """ Mycursor.exe cute (slct) # Query matching records in the buffer pool Records = mycursor. fetchall () For record in records: Print record   # End database operations and release the cursor Mycursor. Close () # Submit operations Conn. Commit () # Closing a connection Conn. Close ()

   

  The above is a basic operation, adding the methods and attributes of several objects:

  1. connection object:

   

  Function of method name

  Close () Close the database

  Commit () commits the current transaction

  Rollback () cancels the current transaction

  Cursor () gets the cursor object of the current connection

  Errorhandler (cxn, cur, errcls, errval) as the handle to the cursor

   

   

  2. cursor object attributes and methods:

   

  Attribute method description

  Arraysize specifies the number of records retrieved at a time using the fetchmany () method. The default value is 1.

  Connection creates a connection to this cursor (optional)

  Discription returns the active status of the cursor, including (7 elements) :( name, type_code,

  Display_size, internal_size, precision, scale, null_ OK)

  Name and type_code are required.

  Lastrowid: return the ID of the last update row (Optional). If the database does not support this function, none is returned.

  Rowcount the number of rows returned or affected by the last execute () Operation

  Callproc (func [, argS]) calls a stored procedure

  Close () Close the cursor

  Execute (OP [, argS]) to execute SQL statements or database commands

  Executemany (OP, argS) executes Multiple SQL statements at a time. The number of executed SQL statements is given by arraysize.

  The next row of the fetchone () matching result

  Fetchall () matches all remaining results

  Fetchmany (size-cursor, arraysize) the following rows of the matching result

  _ ITER _ () Create an iteration object (optional, refer to next ())

  Messages cursor executes the list of information returned by the database (Set of tuples)

  Next () use the iteration object to get the next row of the result

  Move nextset () to the next result set (if supported)

  Rownumber index of the cursor in the current result set (starting from 0 rows)

  Setinput-size (sizes): set the maximum input value.

  Setoutput-size (sizes [, Col ])