The website is to interact with the database, otherwise nothing will be done... Today let's look at a database named mysqldb, which is used to interact with the MySQL database.
You can obtain this library from here.
Http://sourceforge.net/projects/mysql-python
If you are not sure whether this library is available in your python environment, open the python shell and input import mysqldb. If the error message is returned, it indicates that there is no such database on your machine, download one. my machine is Windows XP, So I downloaded the EXE in the Windows environment and double-click it to complete the installation. Before introducing specific operations, let's take a moment to talk aboutProgramHow to interact with a database
1. Establish a connection with the database
2. Execute SQL statements and receive the returned values.
3. Close database connection
Follow the steps above to use mysqldb. Let's proceed step by step. [B] 0. Introduce the mysqldb Library [/B]
Import mysqldb [B] 1. Establish a connection with the database [/B]
Conn = mysqldb. Connect (host = "localhost", user = "root", passwd = "sa", DB = "mytable ")
The connect method is used to establish a connection with the database, receive several parameters, and return the connection object. 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: name of the database to be used. No default value exists.
Port: the TCP port used by the MySQL service. The default value is 3306.
For more information about the parameters, see here.
Http://mysql-python.sourceforge.net/MySQLdb.html Then, this connection object also provides support for transaction operations, standard methods
Commit () Submit
Rollback () rollback [B] 2. Execute SQL statements and receive return values [/B]
Cursor = conn. cursor ()
N=cursor.exe cute (SQL, Param)
First, we use the connection object to obtain a cursor object. Next, we will use the method provided by cursor to work. These methods include: 1. execute commands; 2. Receive returned values. Cursor is used to execute commands:
Callproc (self, procname, argS): used to execute a stored procedure. The received parameters are the stored procedure name and parameter list. The returned values are the number of affected rows.
Execute (self, 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 (self, query, argS): executes a single-pick SQL statement, but repeats the parameters in the parameter list. The returned value is the number of affected rows.
Nextset (Self): Move to the next result set Cursor is used to receive the returned value:
Fetchall (Self): receives all returned result rows.
Fetchmany (self, 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 (Self): returns a result line.
Scroll (self, 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. The followingCodeIs a complete example.
# When using SQL statements, the parameters to be received here use 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 )"
# Param should be tuple or list
Param = (title, singer, imgurl, URL, alpha)
# Execution. If the execution succeeds, the value of N is 1.
N=cursor.exe cute (SQL, Param) # Execute another query operation
Cursor.exe cute ("select * From cdinfo ")
# We use the fetchall method. In this way, all results returned by the query will be saved in the CDs. Each result is a tuple-type data, which forms a tuple.
CDS = cursor. fetchall ()
# Because it is a tuple, you can use the result set in this way.
Print CDs [0] [3]
# Or directly display the result set to see how it looks.
Print CDs # If you want to insert data in batches, do this.
SQL = "insert into cdinfo values (0, % s )"
# The set of each value is a tuple, and the entire parameter set is a tuple or list
Param = (title, singer, imgurl, URL, alpha), (title2, singer2, imgurl2, url2, alpha2 ))
# Use the executeplugin method to insert data in batches. This is a really cool method!
N=cursor.exe cute.pdf (SQL, Param) Note that (or I am surprised) After performing the insert, delete, or modify operation, you need to call Conn. commit using the Commit () method. in this way, the data is actually stored in the database. I don't know whether it is my MySQL settings. In short, if I didn't use commit at the very beginning today, the data will not be retained in the database. However, the data is indeed in the database. because the automatic number is accumulated, and the number of affected rows returned is not 0. [B] 3. Disable database connection [/B]
You need to close the pointer object and connection object respectively. They have the same name.
Cursor. Close ()
Conn. Close () The basic database operations are as follows:
Mysqldb User Guide: http://mysql-python.sourceforge.net/MySQLdb.html
Mysqldb documentation: http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html |
