20, MySQLdb

MySQLdb

• Win64 bit installation python-mysqldb1.2.5

• Install MySQLdb under Ubuntu

  sudo apt-get install python-MySQLdb

• Import MYSQLDB Library

  import MySQLdb

• Create a database connection

  conn = MySQLdb.connect(host="localhost",user="root",passwd="123456",db="test",charset="utf8")

• Connect Object Properties
  • commit(): If the database table has been modified, commit to save the current data. Of course, if this user does not have permission to do it, nothing will happen.
  • rollback(): If you have permission, cancel the current operation, or an error.
  • cursor([cursorclass]): Cursor pointer.

• Creating cursors (Pointers) cursor

  cur = conn.cursor()

• Cursor how to execute the command:
  • execute(query, args): Executes a single SQL statement. Query is the SQL statement itself, and args is the list of parameter values. The return value after execution is the number of rows affected.
  • executemany(query, args): Executes a single SQL statement, but repeats the parameters in the list of parameters and returns the number of rows affected

• Insert a record in a data table

  cur.execute("insert into users (username,password,email) values (%s,%s,%s)",("python","123456","[email protected]"))

• Inserting multiple records in a data table

  cur.executemany("insert into users (username,password,email) values (%s,%s,%s)",(("google","111222","[email protected]"),("facebook","222333","[email protected]"),("github","333444","[email protected]"),("docker","444555","[email protected]")))

• Commit Database Operations

  conn.commit()

• Querying data

    cur.execute ("SELECT * from users")  

  • Cursor object methods for getting data
    • fetchall (self) : receives all the returned result rows.
    • Fetchmany (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 data is returned.
    • Fetchone () : Returns a result row.

    • Scroll (value, mode= ' relative ') : Moves the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, if Mode= ' Absolute ', the value bar is moved from the first row of the result set.

        Cur.execute ("SELECT * from users") lines = Cur.fetchall () for line in Lines:print linecur.execute ("SELECT * F Rom users where id=1 ") Line_first = Cur.fetchone () #只返回一条print line_firstcur.execute (" SELECT * from users ") print Cur.fe Tchall ()  

• Cursors cursor Operations

  • cur.scroll(n)Or cur.scroll(n,"relative") : means move up or down relative to the current position, n is positive, indicates down (forward), n is negative, indicates up (backward)

  • There is also a way to achieve "absolute" movement, not "relative" movement: Add a parameter "absolute"

    cur.scroll(1)cur.scroll(-2)cur.scroll(2,"absolute")    #回到序号是2,但指向第三条

• Update data

  cur.execute("update users set username=%s where id=2",("mypython"))conn.commit()

• Specify the database

  conn = MySQLdb.connect("localhost","root","123456",port=3306,charset="utf8")    #创建数据库时不指定那个数据库conn.select_db("test")      #连接创建后再指定

• Close the database

  cur.close()     #先关闭游标conn.close()    #再关闭数据库

20, MySQLdb