Python about MySQL operations

One. Install MySQL

Windows, download the MySQL installation file directly, double-click the installation file next to proceed;

Linux installation is also very simple, in addition to download the installation package for installation, the General Linux warehouse will have MySQL, we only need a command to download the installation:

Ubuntu, Deepin

sudo apt-get install Mysql-server

Sudo Apt-get Install Mysql-client

CentOS, Redhat

Yum install MySQL

Two. Installing Mysql-python

To enable Python to operate MySQL requires the Mysql-python driver, which is a necessary module for Python to operate MySQL.

: https://pypi.python.org/pypi/MySQL-python/

After downloading the Mysql-python-1.2.5.zip file, unzip it directly and enter the mysql-python-1.2.5 directory to execute the following statement:

Python setup.py Install

Three. Test whether the installation is successful

Execute the following statement to see if the error, no error description MYSQLDB module installation success

Import MySQLdb

Four. MySQL basic operation

View all current Databases

show databases;

Select Database

Use test;

View the table below the test library

Show tables;

Create a msg table, including ID, title, name, content four fields

CREATE TABLE ' msg ' (

' ID ' int primary key auto_increment,

' title ' varchar (60),

' Name ' varchar (10),

' Content ' varchar (1000)

);

Inserting data into the MSG table

INSERT INTO MSG

(id,title,name,content)

Values

(1, ' test01 ', ' zhzhgo01 ', ' Test content01 '),

(2, ' test02 ', ' zhzhgo02 ', ' Test content02 '),

(3, ' test03 ', ' zhzhgo03 ', ' Test content03 '),

(4, ' test04 ', ' zhzhgo04 ', ' Test content04 ');

(5, ' test05 ', ' zhzhgo05 ', ' Test content05 ');

View data for MSG tables

select * from user;

Modify the content of name equals zhzhgo05 to test05

Update msg set content= ' test05 ' where name= ' zhzhgo05 ';

To delete name=zhzhgo05 data

Delete from msg where name= ' zhzhgo05 ';

View Table Contents

Myselect * from MSG;

Give the root user full access to the 127.0.0.1 machine, with a password of 123456 and immediate effect

Grant all privileges on * * to ' root ' @ ' 127.0.0.1 ' identified by ' 123456 '

Flash privileges

Five. Python Operation MySQL Database

1. Basic operation

Import MySQLdb

Conn=mysqldb.connect (host= "127.0.0.1", user= "root", \

Passwd= "123456", db= "test", \

port=3306,charset= "UTF8")

The Connect () method is used to create a connection to the database, where you can specify parameters: host, user name, password, selected database, port, character set, which is just connected to the database, and you need to create a cursor to manipulate the database.

Cur=conn.cursor ()

Create a cursor by getting to the cursor () method under the database connection conn

N=cur.execute (Sql,param)

Use the cursor cur operation execute () method to write SQL statements to manipulate the data, returning the number of affected items

Cur.close ()

Close Cursors

Conn.commit ()

Commit things, you must have this method when inserting a piece of data into the database, otherwise the data will not be really inserted

Conn.close ()

To close a database connection

2. Inserting data

cur.execute ( "INSERT into MSG values (' test05 ', ' Zhzhgo05 ', ' Test content05 ') ' ' can insert a single piece of data, but it is not convenient to insert multiple data at this time, you can use the Excutemany () method, see the following example:

Import Mysqldbconn=mysqldb.connect (host= "127.0.0.1", user= "root", passwd= "123456", db= "test", port=3306,charset= "UTF8") cur = conn.cursor () #一次插入多条记录sql = "INSERT into MSG values (%s,%s,%s)" #executemany () method can be Insert multiple values, execute a single SQL statement, but repeat the parameters in the list of parameters, the return value is the number of rows affected Cur.executemany (sql,[(' test05 ', ' zhzhgo05 ', ' Test content05 '), (' test06 ', ' zhzhgo06 ', ' Test content06 '), (' test07 ', ' zhzhgo07 ', ' Test content07 '),]) Cur.close () Conn.commit () Conn.close ()

If you want to insert 1000 data into the table how to do it, obviously with the above method is difficult to achieve, if only test data, then you can use for loop, existing user table, field ID self-increment, gender random, see the following code:

Import Mysqldbimport randomconn=mysqldb.connect (host= "127.0.0.1", user= "root", passwd= "", db= "test", port=3306,charset= "UTF8") cur=conn.cursor () sql= "INSERT into User (Name,gender) values" For I in range (1000) : sql+= "(' User" +str (i) + "'," +str (Random.randint (0,1)) + ")," Sql=sql[:-1]print sqlcur.execute (SQL)

3. Querying data

Executing Cur.execute ("SELECT * from MSG") to query data in a datasheet does not print out the data in the table, as follows:

Import Mysqldbconn=mysqldb.connect (host= "127.0.0.1", user= "root", passwd= "", db= "test", port=3306,charset= "UTF8") cur=conn.cursor () sql= ' select * from msg ' n=cur.execute (SQL) print n

What we get is just how many data we have in our table, and we don't print the data.

Describes several commonly used functions:

Fetchall (): Receive all returned result rows

Fetchmany (Size=none): Receives the size bar to return the result row,

If the value of size is greater than the number of result rows returned,

The cursor.arraysize data is returned

Fetchone (): Returns a result row

Scroll (value,mode= ' relative '): Moves the pointer to a line,

If mode= ' relative ', it means moving the value bar from the current row,

If mode= ' absolute ', it means moving the value bar from the first row of the result set

Look at the following example:

Import Mysqldbconn=mysqldb.connect (host= "127.0.0.1", user= "root", passwd= "", db= "test", port=3306,charset= "UTF8") cur=conn.cursor () sql= ' select * from msg ' n=cur.execute (SQL) print Cur.fetchall () print "----- --------------"Cur.scroll (1,mode= ' absolute ') print Cur.fetchmany (1) print"-------------------"Cur.scroll (1,mode= ' Relative ') print Cur.fetchmany (1) print "-------------------" cur.scroll (0,mode= ' absolute ') row=cur.fetchone () while Row:print row[2] Row=cur.fetchone () cur.close () Conn.close ()

The results of the implementation are as follows:

>>>

((1L, U ' test01 ', U ' zhzhgo01 ', u ' Test content01 '), (2L, U ' test02 ', U ' zhzhgo02 ', u ' Test content02 '), (3L, U ' test03 ', U ' zhzhg O03 ', u ' Test content03 '), (4L, U ' test04 ', U ' zhzhgo04 ', u ' Test content04 '))

-------------------

((2L, U ' test02 ', U ' zhzhgo02 ', u ' Test content02 '),)

-------------------

((4L, U ' test04 ', U ' zhzhgo04 ', u ' Test content04 '),)

-------------------

Zhzhgo01

Zhzhgo02

Zhzhgo03

zhzhgo04

>>>

This article from "Today's efforts, tomorrow's success!" "Blog, be sure to keep this provenance http://zhzhgo.blog.51cto.com/10497096/1678319

Python about MySQL operations