Use of MySQLdb library in python

In the development process, the interaction with the database cannot be avoided. In the actual environment, the most popular Mysql database is used. How does python interact with Mysql, python uses a library named MySQLdb to connect to MySQL. Well, the following describes how to install, connect to MySQL, execute SQL statements, obtain results, and disable database connections from MySQLdb:

1. Install MySQLdb

   I am using the ubuntu system. The installation method is apt-get install python-MySQLdb. In this way, when you execute import MySQLdb In the python environment, no error is reported.

   root@ubuntu:~# pythonPython 2.7.4 (default, Apr 19 2013, 18:32:33)[GCC 4.7.3] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import MySQLdb>>>

2. How to connect to Mysql

   The connect method provided by MySQLdb is used to establish a connection with the database. It receives several parameters and returns the connection object, for example, conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "sa", db = "mytable", port = 3306)

   Common parameters include:
   Host: specifies the Database host name. The local host is used by default.
   User: Database login name. The default value is the current user.
   Passwd: Password for database login. Empty by default.
   Db: the name of the database to be used. There is no default value. If db is set here, the database is directly connected to the database set in the Mysql database.
   Port: the TCP port used by the MySQL service. The default value is 3306.

   Note: The host, user, passwd, and so on in connect can be left empty. Only when writing data can be in the order of host, user, passwd, db (can be left empty), and port, note that port = 3306 should not be omitted. If no db is in front of port, an error will be reported if you write 3306 directly.

   After the connection is successful, if you need to switch to another database of the user, use the following statement: conn. select_db ('mysql') to switch to the database

   >>> con=MySQLdb.connect('localhost','root','123456',port=3306)>>> con.select_db('mysql')>>> cur=con.cursor()>>> cur.execute('show tables')24L>>> cur.fetchall()(('columns_priv',), ('db',), ('event',), ('func',), ('general_log',), ('help_category',), ('help_keyword',), ('help_relation',), ('help_topic',), ('host',), ('ndb_binlog_index',), ('plugin',), ('proc',), ('procs_priv',), ('proxies_priv',), ('servers',), ('slow_log',), ('tables_priv',), ('time_zone',), ('time_zone_leap_second',), ('time_zone_name',), ('time_zone_transition',), ('time_zone_transition_type',), ('user',))

   Row 1st: connect to database row 2nd: select to connect to mysql. The following are the rows in this database to obtain the database table. The syntax will be described later.

3. How to operate the database, MySQLdb uses cursor pointer) cursor method to operate the database

   Because the underlying layer of this module actually calls c api, you must first obtain the pointer to the database.

   >>> cur=con.cursor()

4. Database Operations and Result Display

   We use the method provided by cursor to perform operations. The main method is 1. Execute the command 2. Receive the result

   Ursor is used to execute commands:
   Execute (query, args): executes a single SQL statement. The received parameters are the SQL statement itself and the list of parameters used. The returned values are the affected rows.
   Executemany (query, args): executes a single-pick SQL statement, but repeats the execution of parameters in the parameter list. The returned value is the number of affected rows.
   
   Cursor is used to receive the returned value:
   Fetchall (self): receives all returned result rows.
   Fetchmany (size = None): receives the size of returned results rows. If the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.
   Fetchone (): returns a result line.
   Scroll (value, mode = 'relative '): Move 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.

   Let's take a look at the operations for adding, deleting, modifying, and querying execute.

   # Create a database 51 ctotest >>> cur.exe cute ('create database 51ctotest') # select database 51 ctotest >>> con. select_db ('51ctotest') # create a table 51cto, id auto-increment> cur.exe cute ('create table if not exists 51cto (id int (11) primary key AUTO_INCREMENT, name varchar (20), age int (3) ') # insert a row of data, assign values to name and age only, and auto-increment id # use an SQL statement, the parameters to be received here use the % s placeholder. note that no matter what type of data you want to insert is #, The placeholder will always use % s, the following values are tuples or lists >>> cur.exe cute ("insert into 51cto (name, age) values (% s, % s)", ('fan ', 25 )) # Insert multiple rows of data. Use executeiterator to insert all values in the following tuples cyclically >>> cur.exe cute.pdf ("insert into 51cto (name, age) values (% s, % s) ", ('te', 25), ('fei', 26), ('musha', 25 ))) 3L # query >>> cur.exe cute ('select * from 51cto ') 5L # We use the fetchall method. in this way, all results returned by the query will be saved in cds. each result is a tuple type of data, which forms a tuple >>> cur. fetchall () (1L, 'fan ', 25L), (2L, 'fan', 25L), (3L, 'te', 25L), (4L, 'fei', 26L), (5L, 'musha', 25L) # If you query again, no query result is displayed, regardless of whether fetchone or fetc The hall and fetchmany pointers move. Therefore, if the pointer is not reset, The fetchall information will only contain the row content after the pointer. Use fetchall to move the pointer to the end. You can use scroll to manually move the pointer to a position> cur. fetchall () >>> cur. scroll (1, 'absolute ') >>> for I in cur. fetchall ():... print I... (2L, 'fan ', 25L) (3L, 'te', 25L) (4L, 'fei', 26L) (5L, 'musha', 25L)

   Scroll:
   

   Cur. scroll (int, parm:
   Int: number of rows to be moved, an integer. In relative mode, positive values move downward, and negative values move upward.
   Parm: The moving mode. The default mode is relative. The relative mode is acceptable. The absolute mode is absoulte.

   # Fetchone takes only one row at a time, and the pointer moves down fetchmanysize) removes size rows at a time >>> cur. scroll (1, 'absolute ') >>> cur. fetchone () (2L, 'fan ', 25L) >>> cur. fetchmany (2) (3L, 'te', 25L), (4L, 'fei', 26L, so how can we get it into the dictionary format? In MySQLdb, DictCursor is included. To do this, it is also very easy to set up a database connection to pass the cusorclass parameter, you can also pass the cusorclass parameter >>> cur = con when getting the Cursor object. cursor (cursorclass = MySQLdb. cursors. dictCursor) >>> cur.exe cute ('select * from 51cto ') 5L >>> for I in cur. fetchall ():... print I... {'age': 25L, 'id': 2L, 'name': 'fan'} {'age': 25L, 'id': 3L, 'name ': 'te'} {'age': 26L, 'id': 4L, 'name': 'fei'} {'age': 25L, 'id': 5L, 'name': 'musha'} # update, used to use % s >>> cur.exe cute ('Update 51cto set name = % s where id = 3 ', ('ms') >>> cur. scroll (2, 'absolute ') >>> cur. fetchone () {'age': 25L, 'id': 3L, 'name': 'ms'} # After the insert, delete, or modify operation is completed, you need to call conn. commit using the commit () method. in this way, the data is actually stored in the database >>> con. commit () # Close the cursor and close the connection >>> cur. close () >>> con. close ()

   
   

This article from the "linux open source-continuous summary..." blog, please be sure to keep this source http://fantefei.blog.51cto.com/2229719/1282443