Configuring the MySQL database in Python

MySQL Database (1)

Although it is a good idea to save data to disk in file form. But people have invented things that are more formatted and faster and easier to write and read-databases (if you read RTHK's information, they call it "the database"). Wikipedia has a more detailed description of the database:

So far, there are three types of data on Earth:

• Relational databases: MySQL, Microsoft Access, SQL Server, Oracle 、...
• Non-relational database: MongoDB, BigTable (Google) 、...
• Key values Database: Apache Cassandra (Facebook), LevelDB (Google) ...

In this tutorial, we mainly introduce the common open source database, where MySQL is the typical representative.

Overview

MySQL is a very extensive database that is used by many websites. There are many legends about this database. For example, Wikipedia says:

MySQL (Official pronunciation in English:/ma??? Skju??? l/"My S-q-l", [1], but also often read as English pronunciation:/ma?? si?kw?l/"My Sequel" was originally an open source relational database management system, the original developer for the Swedish MySQL AB company, the company in 2008 was raised Yang Micro-system (sun Microsystems) acquisitions. In 2009, Oracle (Oracle) acquired Sun Micro Systems, and MySQL became a product of Oracle's.

MySQL has become the most popular open source database in the past due to its high performance, low cost and good reliability, so it is widely used in small and medium-sized websites on the internet. As MySQL matures, it is increasingly used for more large-scale websites and applications such as Wikipedia, Google and Facebook. The very popular open source software combination of "M" in lamp refers to MySQL.

Oracle, however, has sharply increased the price of the MySQL commercial edition, and Oracle no longer supports the development of another free software project, OpenSolaris, As a result, the free software community has some concerns about whether Oracle will continue to support MySQL Community Edition (the only free version of MySQL), so some of the original open source software that uses MySQL is moving to other databases. For example, Wikipedia was formally announced in 2013 to migrate from MySQL to the MARIADB database.

Anyway, MySQL is still a good database choice, enough to support readers to complete a fairly small site.

Installation

Your computer may not be born with MySQL (is there an operating system that is built in when it is installed?). There is, therefore, a particular distribution of Linux is recommended, and it is essentially a program that needs to be installed if necessary.

I use the Ubuntu operating system demo, because I believe that readers in the future in real projects, most of the situation is to operate the Linux system server, and, I love to use Ubuntu. Also, the goal of this tutorial is from beginner to master, regardless of whether it is true master, always pretend to be like, Linux can give you veneer.

The first step is to run the following command on the shell side:

install mysql-server

After running, the database is installed. Isn't it simple? Of course, the configuration is also needed.

Step two, configure MySQL

After installation, run:

service mysqld start

Start the MySQL database. Then do the following to configure it.

The default MySQL installation after the root user is no password, note that there is a noun "root user", whose user name is: root. Run:

$mysql -u root

Here the reason for using-u root is because I am now a general user (Firehare), if you do not add-u root, MySQL will assume that it is firehare log in.

After entering MySQL, you will see the beginning of the > symbol, which is the command interface of MySQL.

The root user password in MySQL is set below, otherwise the MySQL service is not secure.

mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY "123456";

Using 123456 as the root user's password, should be very foolish, if in the real project, it is best not to do so, to use the uppercase and lowercase letters and numbers mixed with the password, and not less than 8 bits.

In the future if you log in to the database, you can use the password you just set.

Run

After installation, run it and manipulate the database.

$ mysql -u root -pEnter password:

Enter the password for the database, and then appear:

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 373Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

Seeing the content of this interface means that you have entered the data. This data can then be manipulated. For example:

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || carstore           || cutvideo           || itdiffer           || mysql              || performance_schema || test               |+--------------------+

With this command, the currently existing database is listed.

The operation of the database, in addition to the command, you can also use some visual tools. For example, phpMyAdmin is good.

More knowledge of database operations, here is not introduced, readers can refer to the relevant books.

The MySQL database is already installed, but Python cannot operate it yet and continues to install the Python Operations database module--PYTHON-MYSQLDB

Installing PYTHON-MYSQLDB

PYTHON-MYSQLDB is an interface program that Python uses to implement various operations on MySQL data.

In programming, you will encounter a lot of similar interface programs, through the interface program to another object operation. Interface program is like the key, if you want to unlock, people directly with the finger to poke, certainly is not, then must use tools, insert into the lock hole, the lock opened, then, the door opened, you can operate the door inside things. Then the tool to open the lock is the interface program. Who know, with the corresponding key to unlock is the best, if using other tools (such as hammers), may not be convenient (in fact, people, that is, the level of unlocking, if it is thief or a thief or something, good at unlocking, with other tools also convenient), that is, the interface program, coding level is different, are considered factors.

Wordy so much, a word, python-mysqldb is the key to open the MySQL database.

If you want to install the source, you can download it here python-mysqldb:https://pypi.python.org/pypi/mysql-python/

After downloading, you can install it.

This can be done under Ubuntu:

sudo apt-get install build-essential python-dev libmysqlclient-devsudo apt-get install python-MySQLdb

You can also use Pip to install:

install mysql-python

After installation, in Python interactive mode:

import MySQLdb

If you do not error, congratulations, has been installed. If the error, congratulations, you can use the wrong information to improve their computer level, please turn to Google God.

Connecting to a database

To find a wife first, to talk about how to raise their children, the same connection to the database first to establish a database.

$ mysql -u root -pEnter password:

To access the database operation interface:

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 373Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

Enter the following command to create a database:

set utf8;Query OK, 1 row affected (0.00 sec)

Note that the above instruction, if only input: Create Database qiwsirtest, but, I added the character set UTF8, meaning that the database qiwsirtest, encoding is Utf-8, It is not garbled to deposit Chinese characters in this way.

See that line hint: Query OK, 1 row Affected (0.00 sec), it means the database has been established, the name is called: qiwsirtest

Once the database is established, you can use Python to connect to the library named Qiwsirtest with the installed MySQLdb.

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

Explain the meanings of the above commands one by one:

• Host: The address of the MySQL database should be filled in after the equals sign, because the database is on this machine (also known as local), so use localhost, note the quotation marks. If you are on a different server, you should fill in the IP address. Generally small and medium-sized web sites, databases and programs are on the same server (computer), the use of localhost.
• User: Log in to the database username, where the general "root", or pay attention to the quotation marks. Of course, if the reader names a different user name, the database administrator provides a proprietary user name and changes it to the appropriate user. However, different users may have different permissions, so in a program, if you want to manipulate the database, you should also be aware of the permissions that you have. Here with root, you can rest assured, what permissions are there. However, this is something that should be avoided in large systems.
• passwd: Login mysql password for the user account above. The password I used in the example above is "123123". Don't forget the quotation marks.
• DB: Just the database created by the Create command, I built the database name is "Qiwsirtest", or to pay attention to the quotation marks. Crossing if the database name is not this, write the name of the database you created.
• Port: In general, the default port for MySQL is 3306, and when MySQL is installed to the server, the server (computer) is going to provide a access port to it in order to allow network access.
• CharSet: This setting, in many tutorials do not write, the results in the real data storage, the discovery of garbled. Here I will qiwsirtest this database encoding to the UTF-8 format, so that the Chinese characters are allowed to be stored without garbled. Note that in MySQL setup, Utf-8 is written as UTF8, without the middle horizontal line. But when you set the encoding format at the beginning of the Python file and elsewhere, write Utf-8. Remember!

Note: Connect in the host, user, passwd, etc. can not write, only in the time of writing in accordance with the host, user, passwd, db (Can not write), port sequence can be written, port number port=3306 or do not omit the good, If there is no DB in front of Port, direct write 3306 will error.

The connection to the database has been completed.

Configuring the MySQL database in Python