Python USES the database tutorial MySQLdb for Python

This article describes in detail how Python USES MySQLdb for Python to operate a database. The details are as follows:

Generally speaking, the website is to interact with the database, otherwise there is nothing to do. Today we'll examine a library called MySQLdb, which is used to interact with MySQL databases.

You can get the library here:

http://sourceforge.net/projects/mysql-python

If you are not sure whether this library exists in your python environment, open the python shell and import MySQLdb. If an error message is returned, it means you do not have it on your machine.

Before going into the details, let's talk about how a program interacts with a database:

1. Establish a connection to the database
2. Execute the SQL statement and receive the return value
Close the database connection

Follow the steps above to use MySQLdb.

I. introduction of MySQLdb library

import MySQLdb

Ii. Establish a connection with the database:

conn=MySQLdb.connect(host= " localhost " ,user= " root " ,passwd= " sa " ,db= " mytable " )

The connect method is provided to establish a connection to the database, receive several parameters, and return the connection object.

Commonly used parameters include:

Host: database hostname. The default is to use localhost.
User: database login name. The default is the current user.
Passwd: database login secret. Default is empty.
Db: database name to use. No default value.
Port: the TCP port used by the MySQL service. The default is 3306.

More information about parameters can be found here:

http://mysql-python.sourceforge.net/MySQLdb.html

Then, this connection object also provides support for transaction operations, in the standard way:

Commit ()
Rollback () rolled back

Iii. Execute SQL statements and receive return values:

cursor=conn.cursor()

n=cursor.execute(sql,param)

First, we obtain a cursor object by using the connection object, and then we use the methods provided by cursor to do our work

Methods used to execute commands with cursor:

Callproc (self, procname, args): used to execute the stored procedure. The parameters received are the stored procedure name and parameter list, and the return value is the number of affected rows
Execute (self, query, args): executes a single SQL statement, takes the parameters of the SQL statement itself and the list of parameters used, and returns the number of affected rows
Executemany (self, query, args): executes the one-on-one hit SQL statement, but repeats the arguments in the argument list, returning the number of affected rows
Nextset (self): moves to the next result set

Method used to receive return values with cursor:

Fetchall (self): receives all returned rows.
Fetchmany (self, size=None): receives a size bar to return a result row. If the value of size is greater than the number of result rows returned, the cursor. Arraysize bar data is returned.
Fetchone (self): returns a result row.
Scroll (self, value, mode='relative'): moves the pointer to a row. If mode='relative', moves the value bar from the current row, and if mode='absolute', moves the value bar from the first row of the result set.

The following code is a complete example:

# use sql statements , All the parameters to be received here are used %s A placeholder . Here's the thing , Whatever type of data you want to insert , Placeholders should always be used %s

sql= " insert into cdinfo values(%s,%s,%s,%s,%s) " 

#param Should be tuple or list

param=(title,singer,imgurl,url,alpha)

# perform , If successful ,n The value of 1

n=cursor.execute(sql,param)
# Let's do another query 

cursor.execute( " select * from cdinfo " )

# We used fetchall This method . such ,cds All results returned from the query will be saved in . Each result is one tuple Type of data , these tuple Form a tuple

cds=cursor.fetchall()

# Because it is tuple, So you can use the result set this way 

print cds[0][3]

# Or just show it , Let's see what the result set actually looks like 

print cds
# If need batch insert data , Just do it 

sql= " insert into cdinfo values(0,%s,%s,%s,%s,%s) " 

# Each set of values is one tuple, The entire parameter set is composed of one tuple, or list

param=((title,singer,imgurl,url,alpha),(title2,singer2,imgurl2,url2,alpha2))

# use executemany Method to insert data in batches . That's a really cool way to do it !

n=cursor.executemany(sql,param)

It is important to note (or I felt strangely), after the execution of the insert or remove, or modify operations, you need to call the conn.com MIT commit () method. In this way, can a data stored in the database. I don't know whether I mysql setup problem, anyway, today I use in the first place, if it's not a commit, the data is not retained in the database, but the data in a database. Because the automatic numbering for the accumulation, and returns the number of rows affected is not zero.

Iv. Close the database connection:

Need to close the pointer object and connect the object respectively. They have the same name method:

cursor.close()

conn.close()

Three steps complete, and that's all for basic database operations. 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

At present, my headache is the encoding problem of characters. When inserting Chinese characters, there is always a garbled code. When I try to change the encoding, the error of "Data too long for column" will appear again. For this, you can refer to the previous article (link: #).

I hope this article has helped you with your Python programming.