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

First, install MySQL

You can download the latest version directly from the official MySQL website. MySQL is cross-platform. Select the corresponding platform to download the installation file and install it. If you are a Windows user, the installation process is very simple, just follow the wizard step by step. If you are a Linux user, the installation process is also quite simple.

# # Ubuntu / Debian$ sudo apt-get install mysql-server$ sudo apt-get install mysql-client## CentOS / RHEL# yum install-y mysql mysql-server mysql-devel

Basic settings:

# # start mysql# service mysqld start## initialization # / usr/bin/mysql_secure_installation## edit my.cnf, modify the default encoding setting # vi / etc/ my.cnf [client] default-character-set = UTF8 [mysqld] default-storage-engine = INNODBcharacter-set-server = utf8collation-server = utf8_general_ci## verification # service mysqld restart# mysql-u root-pmysql > show variables like'% char%' +-- +-- + | Variable_name | Value | +-- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | Utf8 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-- +-- + 8 rows in set (0.00 sec) 2. Install the MySQL driver

Because the MySQL server runs as a separate process and serves externally over the network, a MySQL driver that supports Python is required to connect to the MySQL server.

Currently, there are two MySQL drivers:

Mysql-connector-python: MySQL's official pure Python driver

MySQL-python: a Python driver that encapsulates the MySQL C driver.

MySQL-python

MySQL-3.23 through 5.5 and Python-2.4 through 2.7 are currently supported. Python-3.0 will be supported in a future release.

Method 1: (recommended)

# pip install MySQL-python

Method 2: (recommended)

# # Ubuntu$ sudo apt-get install python-mysqldb## CentOS# yum install-y MySQL-python

Method 3:

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

After downloading the MySQL-python-1.2.5.zip file, extract it directly. Enter the MySQL-python-1.2.5 directory:

# unzip MySQL-python-1.2.5.zip# cd MySQL-python-1.2.5# python setup.py install

Test whether the installation is successful

The test is very simple, just check that the MySQLdb module can be imported properly.

[root@localhost] # pythonPython 2.6.6 (r266Jul 84292, Jul 23 2015, 15:22:56) [GCC 4.4.7 20120313 (Red Hat 4.4.7-11)] on linux2Type "help", "copyright", "credits" or "license" for more information. > > import MySQLdb

There is no error indicating that the MySQLdb module cannot be found, indicating that OK is installed.

3. Review of basic database operations mysql > show databases / / View all current databases +-+ | Database | +-+ | information_schema | | mysql | +-+ 2 rows in set (0.15 sec) mysql > CREATE DATABASE test / / create database Query OK, 1 row affected (0.17 sec) mysql > use test; Database changedmysql > show tables; / / View database table Empty set (0.00 sec) / / create a table named user mysql > CREATE TABLE user (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR (20), password VARCHAR (20), PRIMARY KEY (id)) engine=innodb default charset=utf8 Query OK, 0 rows affected (0.16 sec) / / insert several pieces of data into the table mysql > INSERT INTO user (name, password) VALUES ('Tom',' 1321'), ('Alen',' 7875'), ('Jack',' 6759'); Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0 Warnings / View user table data mysql > SELECT * FROM user +-4. Python operates the database. | 3 | Jack | 6759 | +-+ 3 rows in set (0.13 sec) 4.

Let's start with a simple example program:

#-*-coding: utf-8-*-import MySQLdb try: conn = MySQLdb.connect (host = 'localhost', user =' root', passwd = '123456, db =' test', port = 3306) cur = conn.cursor () # execute SQL cur.execute ("" CREATE TABLE python (ID INT, COL1 VARCHAR (40)) COL2 VARCHAR (40), COL3 VARCHAR (40) "") cur.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3) VALUES (1,'a', 'baked,' c'), (2, 'aa',' bb', 'cc'), (3,' aaa', 'bbb' 'ccc') ") cur.execute (' select * from python') cur.close () conn.commit () conn.close () except MySQLdb.Error,e: print" Mysql Error% d:% s "% (e.args [0], e.args [1])

Please pay attention to modify your database, hostname, user name and password. When connecting to the database, you should pay attention to the database coding, otherwise a lot of coding problems would be better to uniformly use utf8 coding. To avoid garbled code, you can modify a parameter:

Add an attribute to conn = MySQLdb.Connect (host='localhost', user='root', passwd='123456', db='test'):

Change to:

Conn = MySQLdb.Connect (host='localhost', user='root', passwd='123456', db='test', charset='utf8')

Charset is to be the same as the code of your database. If the database is gb2312, write charset='gb2312'.

1. Increase

#-*-coding: utf-8-*-import MySQLdbconn = MySQLdb.connect (host = 'localhost', port = 3306, user =' root', passwd = '123456, db =' test', charset = 'utf8') cur = conn.cursor () cur.execute ("INSERT INTO user (name, password) VALUES (' python') Cur.close () conn.commit () conn.close () inserts data by writing a pure sql statement in the above execute () method. Such as:

> cur.execute ("insert into user (name, password) values ('python',' 8901')")

If I want to insert new data, I have to change the value in this statement. We can make the following changes:

#-*-coding: utf-8-*-import MySQLdbconn = MySQLdb.connect (host = 'localhost', port = 3306, user =' root', passwd = '123456, db =' test', charset = 'utf8') cur = conn.cursor () sqli = "INSERT INTO user (name, password) VALUES (% s,% s)" cur.execute (sqli, (' chrome') '1903') cur.close () conn.commit () conn.close ()

The executemany () method can insert multiple records at a time

Sqli = "INSERT INTO user (name, password) VALUES (% s,% s)" cur.executemany (sqli, [('IE',' 1021'), ('Firefox',' 9012'), ('windows',' 6213')]))

Best practices:

If you are inserting data, perform a commit operation, otherwise the data will not be written to the database. It's best to commit after all the sql statements have been executed, which turns out to be a big speed boost.

Cur.execute (sqli, (value1, value2)), if the value of value is uncertain, then there is a risk of sql injection in the above two statements.

For example, if value is a piece of html code, if there are quotation marks (single or double quotation marks) in the html code, and if left unprocessed, the sql statement will be truncated and an insertion error will be thrown.

If there is an English comma in the value, it will cause the preceding key to not correspond to the subsequent value, and the data will not be inserted correctly.

If there is a backslash\ in the value, the data behind the backslash in the python will be escaped, resulting in character loss or other unencountered results if the insertion is not handled.

Because there are many symbols with special meaning in SQL and Python, if it can not be handled well, there will be a lot of problems. Fortunately, the MYSQLdb module provides us with an escape_string method, which can automatically deal with the above problems, especially convenient.

Value = MySQLdb.escape_string (value) # this function prevents python strings and SQL escapes

2. Check

Maybe you've tried to pass through python.

> cur.execute ("select * from user")

To query the data in the data table, but it does not print out the data in the table.

Let's see what this statement gets.

> aa=cur.execute ("select * from user")

> print aa

five

All it gets is how many pieces of data there are in our table. So how do you get the data in the table?

#-*-coding: utf-8-*-import MySQLdbconn = MySQLdb.connect (host = 'localhost', port = 3306, user =' root', passwd = '123456, db =' test' Charset = 'utf8') cur = conn.cursor () # get how many records there are in the table sqls = "SELECT * FROM user" num = cur.execute (sqls) print num# print info = cur.fetchmany (num) for line in info: print linecur.close () conn.commit () conn.close ()

There are usually two ways to query:

One is to use cursor.fetchall () to get all the query results, and then iterate one by one

The other gets one record at a time through cursor.fetchone () until the result is empty. Take a look at the following example:

Import MySQLdb try: conn = MySQLdb.connect (host='localhost',user='root',passwd='123456',port=3306) cur = conn.cursor () conn.select_db ('test') sqls = "SELECT id, name, password FROM user" cur.execute (sqls) rows = cur.fetchall () for row in rows: print's%,% s,% s'% (row [0], row [1] Row [2]) print 'Number of rows returned:% s'% (cur.rowcount) # sqls = "SELECT id, name, password FROM user" cur.execute (sqls) while True: row = cur.fetchone () if row = = None: break print'%,% s,% s'% (row [0]) Row [1], row [2] print 'Number of rows returned:% s'% (cur.rowcount) # cur.scroll (0meme mode = 'absolute') # Mobile cursor results = cur.fetchmany (5) for rin results: print r conn.commit () cur.close () conn.close () except MySQLdb.Error E: print "Mysql Error% d:% s"% (e.args [0], e.args [1])

Here are some commonly used functions:

Conn connection objects also provide support for transaction operations, standard methods

Commit () submit

Rollback () rollback

The method used by cursor to execute the command:

Callproc (self, procname, args): used to execute a 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. The parameters received are the sql statement itself and the list of parameters used, and the return value is the number of affected rows.

Executemany (self, query, args): executes a single sql statement, but repeats the parameters in the parameter list. The return value is the number of affected rows.

Nextset (self): move to the next result set

The method used by cursor to receive the return value:

Fetchall (self): receives all the returned result rows.

Fetchmany (self, size=None): receives the size bar and returns the result row. If the value of size is greater than the number of result rows returned, cursor.arraysize data is returned.

Fetchone (self): returns a result row.

Scroll (self, value, mode='relative'): move the pointer to a row. If mode='relative', means moving the value bar from the current row, if mode='absolute', means moving the value bar from the first row of the result set.

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.