Python MySQLdb Common operations

I am using the MySQL database of mysqldb operation. Let's start with a simple example:

Import mysqldbtry:conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' test ', port=3306) cur= Conn.cursor () cur.execute (' select * from user ') Cur.close () conn.close () except Mysqldb.error,e:print "Mysql Error%d:%s "% (E.args[0], e.args[1])

Please note that modify your database, hostname, username, password.

Here's an example of inserting data, inserting data in bulk, and updating data:

Import mysqldb try:    conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' Root ', port=3306)     cur=conn.cursor ()           cur.execute (' Create database if not exists python ')      conn.select_db (' python ')     cur.execute (' Create table test (id int,info  varchar (())          value=[1, ' Hi rollen ']     cur.execute (' Insert into test values (%s,%s) ', value)           values=[]    for i in range (:  )       values.append ((i, ' Hi rollen ' +str (i)))               cur.executemany (' Insert into test values (%s,%s) ', values)       cur.execute (' update test set info= ' I am rollen " where id=3 ')       conn.commit ()     cur.close ()     conn.close ()  except MySQLdb.Error,e:     print  "mysql error %d: %s"  %  (E.args[0], e.args[1])

Please note that you must have conn.commit () to commit the transaction , or you cannot actually insert the data.

After running my MySQL database will not be the result.

Import mysqldbtry:    conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' root ' , port=3306)     cur=conn.cursor ()           conn.select_db (' python ')      count=cur.execute (' Select * from test ')     print  ' There has %s rows record '  % count      result=cur.fetchone ()     print result     print  ' id: %s info %s '  % result     results= Cur.fetchmany (5)     for r in results:         print r     print  ' = = ' *10    cur.scroll (0, Mode= ' absolute ')      results=cur.fetchall ()     for r in  results:        print r[1]           Conn.commit ()     cur.close ()     conn.close ()  except  mysqldb.error,e:     print  "mysql error %d: %s"  %  ( E.ARGS[0], E.ARGS[1])

The result of the operation is not affixed, too long.

The Chinese will display correctly after query, but it is garbled in the database. After I looked up from the Internet, I found that using a property can be done:

In Python code

conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' python ')

Add one of the properties:
Switch

conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' python ', charset= ' UTF8 ')

CharSet is to be the same as your database code, if the database is gb2312, then write charset= ' gb2312 '.

Here are some common functions:

The connection object is then provided with support for transactional operations, and the standard method
Commit () Commit
Rollback () rollback

cursor the method used to execute the command:
execute (self, query, args): Executes a single SQL statement, receives the arguments for the SQL statement itself and the parameter list used, the return value is the number of rows affected
executemany ( Self, query, args): Executes a heads-up SQL statement, but repeats the parameters in the list of parameters, the return value is the number of rows affected
nextset (self): Move to the next result set

cursor method to receive the return value:
fetchall (self): receives all the returned result rows.
fetchmany (Self, Size=none): Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, The Cursor.arraysize bar data is returned.
fetchone (self): Returns a result row.
scroll (self, value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', Moves the value bar from the current row and, if mode= ' absolute ', moves the value bar from the first row of the result set.

Host: String type, specifying the connected host

User: String type, specifying the username of the connection

passwd: String type, specifying the password for the connection

DB: Character channeling type, specifying connected database

Port:integer type, specifying the port number of the connection

Unix_socket: String type, specifying the location of the UNIX socket

CONV: conversion dictionary, reference Mysqldb.converters module

Connect_timeout: The time that the connection timed out, in seconds

Compress: whether to compress

Named_pipe: Named Pipes

Init_command: The first command executed after the connection is established