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

Python operates the MySQL of the database

Install the Python-MySQLdb module

Python-MySQLdb is a module that manipulates the database, through which Python implements various operations on mysql data.

If you want to install source code, you can download it here: https://pypi.Python.org/pypi/MySQL-Python/

Extract the installation package, enter the unzipped directory and execute the following command to install

Python setup.py install

You can do this under ubuntu:

Sudo apt-get install build-essential Python-dev libmysqlclient-devsudo apt-get install Python-MySQLdb

Pip installation:

Pip install mysql-Python

After installation, in python interactive mode:

> import MySQLdb

If you don't report an error, congratulations, it's already been installed.

Second, operate the database

Operation of the database process:

1. Import MySQLdb module

2. Create a database connection

3. Execute SQL statements and stored procedures

4. Close the database connection

Create a MySQL connection object

> import MySQLdb > conn = MySQLdb.connect (host= "localhost", user= "root", passwd= "123456", db= "python", port=3306,charset= "utf8") >

Explanation of the meaning of the command:

Host: the address of the mysql database should be filled in after the equal sign, because the database is on the local machine (also known as local), so use localhost, pay attention to the quotation marks. If you are on another server, you should fill in the ip address here. Generally speaking, small and medium-sized websites, databases and programs are all on the same server (computer), so localhost is used.

User: the user name that logs in to the database. Fill in "root" here, but still pay attention to the quotation marks. Of course, if the reader names another user name and the database administrator provides a proprietary user name, it will be changed to the corresponding user. However, the permissions of different users may be different, so in the program, if you want to manipulate the database, you should also pay attention to the permissions you have. If you use root here, you can rest assured that you have all the permissions. However, doing so should be avoided in large systems.

Passwd: the password for logging in to mysql corresponding to the above user account. The password I used in the above example is "123123". Don't forget the quotation marks.

Db: this is the database I just set up through the create command. The name of the database I set up is "qiwsirtest", but I still need to pay attention to the quotation marks. If the name of the database created by the officer is not this, write the name of the database built by yourself.

Port: in general, the default port for mysql is 3306. When mysql is installed on the server, the server (computer) should provide it with an access port in order to allow network access.

Charset: this setting, which is not written in many tutorials, turns out to be garbled when the data is actually stored. Here I set the code of the qiwsirtest database to utf-8 format, which allows the storage of Chinese characters without garbled codes. Notice that in the mysql setting, utf-8 is written as utf8 and there is no middle line. But when you set the encoding format at the beginning of the Python file and elsewhere, write it as utf-8. Remember!

Python establishes a connection with the data, in fact, it establishes an instance object of MySQLdb.connect (), or generally called the connection object, and Python talks with the database by connecting the object. The common methods for this object are:

Commit (): if the database table has been modified, commit to save the current data. Of course, if this user doesn't have permission, nothing will happen.

Rollback (): if you have permission, cancel the current operation, otherwise an error will be reported.

Cursor ([cursorclass]): returns the connected cursor object. Execute a SQL query through a cursor and check the results. Cursors support more methods than connections, and may be easier to use in programs.

Close (): close the connection. Since then, connection objects and cursors are no longer available.

Create a cursor

After the connection between Python and data is established, to manipulate the database, you need to have Python execute the SQL statement against the database. Python executes SQL statements through cursors. Therefore, after the connection is established, you should use the connection object to get the cursor object, as follows:

> > cur = conn.cursor ()

After that, you can use the method of cursor object to manipulate the database. Then you also need to understand the common methods of cursor objects:

Name

Description

Close ()

Close the cursor. The cursor is not available after that

Execute (query [, args])

Execute a SQL statement that can take parameters

Executemany (query, pseq)

Execute a sql statement on each parameter in the sequence pseq

Fetchone ()

Return a query result

Fetchall ()

Return all query results

Fetchmany ([size])

Returns the size bar result

Nextset ()

Move to the next result

Scroll (value,mode='relative')

Move the cursor to the specified row, if mode='relative', means to move the value bar from the current row, if mode='absolute', means to move the value bar from the first row of the result set

Insert data

> cur.execute ("insert into user (name,age,mail) values (% SDH% s)", ("lulu", 18, "lulu@gmail.com")) 1L >

No error was reported, and a "1L" result was returned, indicating that one n-line record operation was successful.

Log in to MySQL to verify whether the data has been added successfully.

Mysql > select * from user;Empty set (0.00 sec) mysql >

Strangely, I didn't see the inserted data! What's wrong?

In particular, after operating on the database through "cur.execute ()", no error has been reported, which is completely correct, but it does not mean that the data has been submitted to the database. You must also use a method of "MySQLdb.connect" connection object: commit (). To submit the data, that is, to perform the "cur.execute ()" operation, the data must be submitted:

> > conn.commit ()

Log in to MySQL again to see if the data has been added successfully.

Mysql > select * from user +-+ | id | name | age | mail | +-+ | 1 | lulu | 18 | lulu@gmail.com | +-+-- -+ 1 row in set (0.00 sec) mysql >

right enough. This is like writing a text, write the text on the text, does not mean that the text has been retained in the text file, you must execute "CTRL-S" to save. That is, when you manipulate the database through Python, after executing various sql statements with "execute ()", you must run the "commit ()" method of the connection object in order for the already executed effect to be saved.

Insert multiple pieces of data

> cur.executemany ("insert into user (name,age,mail) values (% SDH% s)", (("google", 25, "g@gmail.com"), ("facebook", 18, "f@face.book"), ("github", 20, "git@hub.com"), ("docker", 10, "doc@ker.com")) 4L > > mysql > select * from user +-+ | id | name | age | mail | +-+ | 1 | lulu | 18 | lulu@gmail.com | | 2 | google | 25 | g @ gmail.com | | 3 | facebook | 18 | f@face.book | | 4 | github | 20 | git@hub.com | | 5 | docker | 10 | doc@ker.com | +-+ 5 rows in set (0.00 sec) mysql >

Multiple records were successfully inserted. In "executemany (query, pseq)", query is still a sql statement, but pseq is a tuple at this time, and the element in this tuple is also tuple, and each tuple corresponds to a list of fields in the sql statement. This sentence has actually been executed many times. It's just that the execution process is not shown to us.

Query

If you want to query the data from the database, also use the cursor method to operate.

> cur.execute ("select * from user") 5L > print cur.fetchall () ((1L, uprighlulufang, 18L, uprigmail.com'), (2L, upright googleboxes, 25L, upright gmail.com'), (3L, upright facebookstores, 18L, upright fenestration face.book'), (4L, upright githballs, 20L, upright gittings hub.com'), (5L, upright dockerships, 10L, upright docker.com') >

The things queried from the database with cur.execute () are "stored somewhere that cur can find". To find out these saved things, you need to use cur.fetchall () (or fechone, etc.), and after finding them, they exist as objects. From the above experimental discussion, it is found that the saved object is a tuple, in which each element is a tuple. So, you can take it out one by one with the for loop.

Perform the above operation again

> print cur.fetchall () >

Why is the result empty?

It turns out that the object found through the cursor has a characteristic when reading, that is, the cursor will move. After the first operation of print cur.fetchall (), because everything is printed out, the cursor moves from the first to the last. When the print ends, the cursor is already after the last one. Next, if you print it again, it will be empty, and there will be nothing left behind the last one.

Look at another experiment.

> cur.execute ("select * from user") 5L > print cur.fetchone () (1L, upright lululubrium, 18L, upright luluubrigmail.com') > print cur.fetchone () (2L, upright googleboxes, 25L, upright gmail.com') > print cur.fetchone () (3L, upright facebookings, 18L, upright feneficiface.book') > > print cur.fetchone () (4L, upright githballs, 20L, upright gitituals hub.com') > > print cur.fetchone () 10L, upright docking ker.com') >

This time, instead of printing them all at a time, we can see from the results that the cursor is moving down one line at a time.

Since the cursor moves when manipulating objects stored in memory, can you move the cursor up or to a specified location? Of course, this is scroll ().

> > print cur.fetchone () (5L, upright docking, 10L, upright docker.com') > cur.scroll (- 3) > print cur.fetchone () (3L, upright facebookkeeping, 18L, uplift docking face.book') > cur.scroll (1) > print cur.fetchone () (5L, upright docking, 10L, upright docker.com').

Sure enough, this function can move the cursor, but observe carefully that the way above is to move the cursor up or down relative to the current position. That is:

Cur.scroll (n), or, cur.scroll (n, "relative"): means to move up or down relative to the current position, where n is positive, indicating downward (forward), and n is negative, indicating upward (backward)

There is another way to achieve "absolute" movement, not "relative" movement: add a parameter "absolute"

In particular, it is important to remind the viewer that in Python, the order of sequence objects begins with 0.

> cur.scroll (1, "absolute") goes back to serial number 1, points to item 2 data > print cur.fetchone () (2L, upright googleboxes, 25L, upright gmail.com') > cur.scroll (0, "absolute") returns to serial number 0, points to item 1 data > print cur.fetchone () (1L, upriluluvial, 18L, upright lululuqmail.com') >

Continue the above operation. Continue.

> > print cur.fetchmany (3) ((2L, upright googleboxes, 25L, upright gmail.com'), (3L, upright facebookings, 18L, upright fenestration face.book'), (4L, upright githballs, 20L, upright gittings hub.com')) >

The above operation is to list three records down from the current position (the cursor points to the position where the sequence number of the tuple is 1, that is, the second record).

Update data

Updates, like inserts, require commit () to submit and save.

> cur.execute ("update user set name=%s where id=5", ("apple",)) 1L > cur.execute ("select * from user where id=5") 1L > print cur.fetchone () (5L, upright appleholder, 10L, upright docking ker.com') >

Submit updates

> > conn.commit () >

Finally, close the cursor and close the connection object

> cur.close () > conn.close () >

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.