Cursor ()-Database connection Operation Python

python operations database, to install a Python and database interaction package Mysql-python-1.2.2.win32-py2.5.exe, then we can use MYSQLDB this package for database operations.

The procedure is as follows:
1. Establish database connection
Import MySQLdb
Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "sa", db= "MyTable")
Cursor=conn.cursor ()
2. Perform database operations
N=cursor.execute (Sql,param)
We're going to use the connection object to get a cursor object, and then we'll use the method provided by the cursor to do the work.
These methods include two main classes: 1. Execute command, 2. Receive return value

3. Cursor used to execute the command method:

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

4. 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 ', then the value bar is moved from the current row, if mode= ' absolute ', the first from the result set Row to move the value bar.

5, 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.

6. Close the database connection

You need to close the pointer object and the connection object separately. They have the same name.
Cursor.close ()
Conn.close ()

Django Operations Database
Django is a great web framework for Python. The Django connection has an API for manipulating the database, which is very simple to use. We configure the database to be connected in settings.py, and then write the business logic separately in modules, view, and URLs.

Cursor ()-Database connection Operation Python