Shulou(Shulou.com)06/01 Report--

Anti-counterfeiting code: by the forgotten Childe

MySQLdb is a module used to control the mysql database through python.

Before we talk about this module, let's consider the steps to operate the mysql database in command-line mode, which are generally divided into three steps:

1. Connect to the database

2. Operate the database (through the sql statement)

3. Disconnect the database

According to this line of thinking, MySQLdb in python also needs to go through these three steps, but it is not executed manually in command line mode.

If you want to use MySQLdb, you must make sure that MySQL-python is installed on the system.

This article is mainly about the use of the MySQLdb module. As for the installation steps, please check the Internet by yourself.

The MySQLdb module provides the following sub-modules internally:

Connections: specifically used to establish connections

Constants (package)

Converters: implement the conversion of strings in python into data types that mysql can handle

Cursors: cursor. Send sql statements based on cursors to get the execution result

Release

Times

Common methods for MySQLdb modules:

When you execute the following method, the preceding connect and cursor are replaced with the name of the object they instantiated.

Connect (* args, * * kwargs): connect to the database. The common parameters are as follows

User='Username'

Passwd='Password'

Host='Ip_Address'

Db='dbname' # which db is the default action?

Port='Port_number'

Connect_timeout=' connection timeout'

Compress: data transmission after compression

Connect.stat (): view connection status

Connect.ping (): test whether the connected mysql server is online. If not, try to reconnect.

Connect.commit (): commit the current transaction

Connect.rollback (): rollback the transaction

Connect.autocommit (self,on): sets the auto-commit transaction feature, which is enabled when the parameter value is 1 and disabled when the parameter value is 0

Connect.thread_id (): get the thread number

Connect.shutdown (): shut down the mysql service (provided the connected user has permission to shut down the mysql service)

Connect.cursor (self, cursorclass=None): create a cursor object through a connect connection object

Connect.select_db ('dbname'): select a database for operation

Cursor.execute (self, query, args=None): executes a single statement through a cursor cursor object

Cursor.executemany (self, query, args): executes multiple statements through cursor cursor objects

The parameter args here must be a tuple sequence type, which can be a tuple in a tuple or a tuple in a list

Cursor.fetchone (self): fetches a single record after the query statement is executed through the cursor cursor object.

The fetchone method can only fetch one record at a time

You can get all the records by traversing the loop

By default, you can only check from top to bottom, not from bottom to top.

Cursor.fetchmany (self, size=None): fetches multiple records after a query statement through a cursor cursor object

The size parameter is used to set how many records are taken out.

For example, cursor.fetchmany (10) indicates that 10 records are taken out

Cursor.fetchall (self): fetches all records after the query statement is executed through the cursor cursor object

Cursor.scroll (self, value, mode='relative'): controls the position of the data pointer

Value parameters:

Used to set the offset, that is, how much position to move

Mode parameters:

The default is relative, that is, offset from the current location

Absolute position when set to absolute, offset from scratch

For example, cursor.scroll (0 absolute) means to move the pointer to the head, starting from scratch, moving 0 bits.

Next, let's demonstrate how to use the MySQLdb module to add, delete, modify and query mysql:

The database we want to operate here is called aproduct, and there is a table test2 in the aproduct library, which is queried as follows:

Mysql > select * from test2 +-+ | name | age | job | city | +-+ | tom | 30 | teacher | SH | | jerry | 25 | doctor | WC | +- +-+ 2 rows in set (0.09 sec)

Create a connection object:

Conn = MySQLdb.connect (username records roodies, passwdstones, abc123, hands, hostcards, 127.0.0.1')

Create a cursor object through a connect connection object:

Cur = conn.cursor ()

Through the above two steps, you can connect to a database normally, and then you can manipulate the database.

Generally speaking, operating the database through python refers to the addition, deletion, modification and query of the database. As for the creation of the database, it is best to operate under the database command line interface.

Suppose we want to ss_product this library to operate, we need to use use ss_product under the command line, but there is no use command under python, but we can do this through select_db:

Conn.select_db ('aproduct')

Execute the sql statement through the cursor cursor object:

As we saw earlier, the test2 table has four fields, and then we insert a record for it.

Cur.execute ("insert into test2 (name,age,job,city) value ('sean',28,'Engineer','SH')")

Let's see if the data has been inserted successfully under the command line:

Mysql > select * from test2 +-+ | name | age | city | +-+ | tom | 30 | teacher | SH | | jerry | 25 | doctor | WC | | sean | 28 | Engineer | SH | +-- -+ 3 rows in set (0.00 sec)

It can be seen that the data has been inserted successfully, but it will be troublesome to insert the data in this way.

Because the data cannot be fixed and it is impossible to modify the SQL statement every time the data is inserted, we usually put the sql statement in a variable name.

Sqli = "insert into test2 (name,age,job,city) value (% s abc',30,'actor','WH'% s)" cur.execute (sqli, ('abc',30,'actor','WH'))

Check the command line to see if the insert is successful:

Mysql > select * from test2 +-+ | name | age | city | +-+ | tom | 30 | teacher | SH | | jerry | 25 | doctor | WC | | sean | 28 | Engineer | SH | abc | 30 | actor | WH | | +-+ 4 rows in set (0.00 sec) |

As you can see, the data has been inserted successfully. This approach is much more flexible than the previous method of writing dead data.

However, you can only insert one piece of data at a time, and to insert more than one piece of data, you need to do something like this:

Sqlim = "insert into test2 (name,age,job,city) values (% s)% s)"

When you want to insert multiple pieces of data, you usually put all the data under the values. Although you don't know how much data to insert, the fields are fixed.

In this case, there are four fields, so we only need to give four% s placeholders in values. Here we can only use% s for placeholders.

The execute method of the cursor object (cursor) can only insert one piece of data at the same time, and if you want to insert more than one piece of data at the same time, use the executemany method:

Cur.executemany (sqlim, [('a123jewelry 40 miners teacherbadge NC')), (' a456 juggernauts 34 pencils Engineerrewrecks BJ'), (('lisi',25,'worker','GZ')]))

Note: the parameters after sqlim must be of a tuple sequence type, either within a tuple or a list containing tuples.

Look at the contents of the database table under the command line:

Mysql > select * from test2 +-+ | name | age | city | +-+ | tom | 30 | teacher | SH | | jerry | 25 | doctor | WC | | sean | 28 | Engineer | SH | abc | 30 | actor | WH | | A123 | 40 | teacher | NC | | a456 | 34 | Engineer | BJ | | lisi | 25 | worker | GZ | +-+ 7 rows in set (0.00 sec) |

Next we try to delete the record "lisi" with the same command:

Sqld = "delete from test2 where name='lisi'" cur.execute (sqld)

Look at the database results:

Mysql > select * from test2 +-+ | name | age | city | +-+ | tom | 30 | teacher | SH | | jerry | 25 | doctor | WC | | sean | 28 | Engineer | SH | abc | 30 | actor | WH | | A123 | 40 | teacher | NC | | a456 | 34 | Engineer | BJ | +-+ 6 rows in set (0.00 sec) |

Next, let's change the age in the record named abc to 35:

Sqlu = "update test2 set age=35 where name='abc'" cur.execute (sqlu)

Go to the database to see the results:

Mysql > select * from test2 +-+ | name | age | city | +-+ | tom | 30 | teacher | SH | | jerry | 25 | doctor | WC | | sean | 28 | Engineer | SH | abc | 35 | actor | WH | | A123 | 40 | teacher | NC | | a456 | 34 | Engineer | BJ | +-+ 6 rows in set (0.00 sec) |

Let's take a look at how to query:

When we use the SQL statement to query on the command line, we return a table of the type shown in the result of the example above.

However, in python, the MySQLdb module can not print out this format, so we need to use another method to achieve the query function.

Sqls = "select * from test2" cur.execute (sqls) cur.fetchone () # query one record cur.fetchmany (6) # query six records

The query in the MySQLdb module is controlled by cursors, and we can control cursors through the scroll method of the cursor cursor object.

When the sql statement is finished, we need to disconnect the database, which is divided into the following steps:

Cur.close () # disconnect cursor conn.close () # disconnect database

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.