[Python] MySQLdb for Python usage guide/python database operations

Web site is to interact with the database, or do not have to do anything ... Today we look at a library called MySQLdb, which is used to interact with the MySQL database. You can get this library from here. Http://sourceforge.net/projects/mysql-python If you're not sure if you have this library in your Python environment, open the Python shell, enter import mysqldb, and if you return an error message, it means you're not on your machine, so download one. My machine is Win XP, So I downloaded the win environment EXE that, directly double-click to complete the installation. Before you introduce a specific operation, take a moment to say how a program interacts with the database. 1. Establish a connection to the database 2. Execute the SQL statement and receive the return value 3. Close the database connection Use MYSQLDB also follow the steps above. Let's take a step-by-step. [b] 0. Introduction of MYSQLDB library [/b] Import MySQLdb [b] 1. Establish a connection to the database [/b] Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "sa", db= "MyTable") The Connect method provided is used to establish a connection to the database, receive several parameters, and return the connection object. The more commonly used parameters include Host: the database hostname. By default, the local host is used. User: Database login name. The default is the current user. PASSWD: The Secret of database landing. Default is empty. DB: The name of the database to use. No default value. The TCP port used by the Port:mysql service. The default is 3306. More information on the parameters can be found here. Http://mysql-python.sourceforge.net/MySQLdb.html The connection object is then provided with support for transactional operations, and the standard method Commit () Commit Rollback () rollback [b] 2. Execute SQL statements and receive return values [/b] Cursor=conn.cursor () N=cursor.execute (Sql,param) First, we use the connection object to get a cursor object, and then we use the method provided by the cursor to do the work. These methods include two main classes: 1. Execute command, 2. Receive return value Cursor the method used to execute the command: Callproc (self, procname, args): Used to execute stored procedure, received parameter is stored procedure name and parameter list, return value is the number of rows affected Execute (Self, query, args): Executes a single SQL statement, receives the parameters for the SQL statement itself and the parameter list used, and returns the number of rows affected Executemany (self, Query, args): Executes a heads-up SQL statement, but repeats the parameters in the list of parameters, with the returned value being the number of rows affected Nextset (self): move to the next result set The cursor is used to receive the return value of the method: 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 data is returned. Fetchone (self): Returns a result row. Scroll (self, value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, if mode= ' absolute ', Represents the move value bar from the first row of the result set. The following code is a complete example. #使用sql语句, the parameters to be received here are in the%s placeholder. Note that no matter what type of data you want to insert, the placeholder will always use%s Sql= "INSERT into cdinfo values (%s,%s,%s,%s,%s)" #param应该为tuple或者list Param= (Title,singer,imgurl,url,alpha) #执行, if successful, the value of n is 1 N=cursor.execute (Sql,param) #再来执行一个查询的操作 Cursor.execute ("SELECT * from Cdinfo") #我们使用了fetchall这个方法. In this way, the CDs will be saved as the full result of the query return. Each result is a tuple-type data that consists of a tuple Cds=cursor.fetchall () #因为是tuple, so you can use result sets like this Print Cds[0][3] #或者直接显示出来 to see what the result set looks like. Print CDs #如果需要批量的插入数据, just do it. Sql= "INSERT into cdinfo values (0,%s,%s,%s,%s,%s)" #每个值的集合为一个tuple, the entire set of parameters consists of a tuple, or list Param= ((Title,singer,imgurl,url,alpha), (TITLE2,SINGER2,IMGURL2,URL2,ALPHA2)) #使用executemany方法来批量的插入数据. This is a really cool way! N=cursor.executemany (Sql,param) Note that (or I'm surprised) that you need to call the Conn.commit () method to commit after the insert or delete or modify operation has been performed. This way, the data is actually saved in the database. I don't know if it's my MySQL setup problem, in short, Today, when I started using it, the data would not remain in the database without a commit, but the data did stay in the database. Because the AutoNumber is cumulative and the number of affected rows returned is not 0. [b] 3. Close the database connection [/b] You need to close the pointer object and the connection object separately. They have the same name. Cursor.close () Conn.close () Three steps to complete, the basic database operation is this. Here are two useful connections MYSQLDB User guide: http://mysql-python.sourceforge.net/MySQLdb.html MySQLdb Document: http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html

[Python] MySQLdb for Python usage guide/python database operations