Python connection MySQL

http://blog.csdn.net/yelbosh/article/details/7498641

Connection to the database

After the introduction of the module we need to connect with the database, the example code is as follows:

db = MySQLdb.connect ("localhost", "root", "123456", "Myciti")

The meaning of these three key parameters is at a glance: The first is the server's address, the second is the user name, the third is the DBMS password, and the fourth is the database to access, in fact, the Connect function parameters more than these, but because it has a default value and in most cases do not have to modify, thus omitted. Here is a list of the following:

• Host, the database server hostname of the connection, by default localhost (localhost).

• User, the username of the connection database, default to the current user.

• passwd, connection password, no default value.

• DB, connected database name, no default value.

• Conv, maps text to a Python-type dictionary. Default is MySQLdb.converters.conversions

• Cursorclass,cursor () The type used, the default value is MySQLdb.cursors.Cursor.

• Compress, enable the Protocol compression feature.

• Named_pipe, in Windows, is connected to a named pipe.

• Init_command, once the connection is established, specify a statement for the database server to run.

• Read_default_file, use the specified MySQL configuration file.

• Read_default_group, read the default group.

• Unix_socket, in Unix, the socket used by the connection uses TCP by default.

• Port, specifying the connection port for the database server, which is 3306 by default

You may notice that the port number is not used in the source code, because the default value of this parameter of the MySQLdb connect function is 3306, if you modify the port number of the database when you install MySQL, you will need to add the parameter's modification value in the source code.

Execute SQL statement

After the connection is executed SQL statement, the source code is as follows:

Import MySQLdb


db = MySQLdb.connect ("localhost", "root", "123456", "Myciti")
cursor = Db.cursor ()
sql = "" INSERT into article values (0, "Woainimahah", "http://www.aa.com", "2012-9-8", "Wo", "QQ", "SKJFASKLFJ", "2019", " Up ")" ""
Try
Cursor.execute (SQL)
Db.commit ()
Except
Db.rollback ()
Db.close

It is important to remember that commit, if not submitted, then the database will not change

Other static operations can be implemented by changing the SQL statement to another, such as "delete from article where ID > 3"

Select and print

The most important purpose of connecting a database is to read the information in the database, then how to get the data in the database? How to extract valid information? See the following code:

The following program implements data for the second column of all rows in the article table in the print database:

Import MySQLdb


db = MySQLdb.connect ("localhost", "root", "123456", "Myciti")
cursor = Db.cursor ()
Cursor.execute ("SELECT * from article")
data = Cursor.fetchone ()
While Data!=none:
Print Data[1]
data = Cursor.fetchone ()
Db.close

We can also use the following code:

Import MySQLdb


db = MySQLdb.connect ("localhost", "root", "123456", "Myciti")
cursor = Db.cursor ()
Cursor.execute ("SELECT * from article")
Datas = Cursor.fetchall ()
For data in Datas:
Print Data[1]
Print Cursor.rowcount, "Rows in Tatal"
Db.close

We can see the difference between the various functions above from the code:

Fetchone is to fetch a row of records from the database table, and the second call takes the next row and keeps going down

Fetchall fetching data from all rows in a database table

RowCount read out the number of rows in a database table

Like placeholders in Java, these placeholders are needed in Python for dynamic selection. See the following code:

Import MySQLdb


A = "Down"
b = 4
db = MySQLdb.connect ("localhost", "root", "123456", "Myciti")
cursor = Db.cursor ()
Cursor.execute ("SELECT * from article where trend = '%s ' and ID < '%d '"% (A, b))
Datas = Cursor.fetchall ()
For data in Datas:
Print Data[1]
Print Cursor.rowcount, "Rows in Tatal"
Db.close

There's a difference between the placeholder and the? Placeholder in Java, which is that it declares the type, and the format output in C is the same. Note the trailing%

Then the returned data is also a list, using the [] operator to access a particular column.

Static inserts and dynamic insertions

Static insert above we have already said, then let's talk about the dynamic insert

Dynamic insertions are also implemented with placeholders.

Import MySQLdb


title = "Wangxinmeiwo"
url = "Henxiangni"
db = MySQLdb.connect ("localhost", "root", "123456", "Myciti")
cursor = Db.cursor ()
sql = "" INSERT into article values (0, "%s", "%s", "2012-9-8", "Wo", "QQ", "SKJFASKLFJ", "2019", "Up") "" "
Try
Cursor.execute (sql% (Title,url))
Db.commit ()
Except
Db.rollback ()
Db.close

You can see that the placeholders here are the same as those used above

Update operation

The use of placeholders is the same as above

Import MySQLdb


title = "Haoxiangni"
id=11
db = MySQLdb.connect ("localhost", "root", "123456", "Myciti")
cursor = Db.cursor ()
sql = "" "Update article Set title ="%s "Where id ="%d "" ""
Try
Cursor.execute (sql% (Title,id))
Db.commit ()
Except
Db.rollback ()
Db.close

Python connection MySQL