Python operation MySQL (i) MYSQLDB module installation and basic database operation

1, Ubuntu Environment installation Python-mysqldbsudo apt-get install build-essential python-dev libmysqlclient-devsudo apt-get installed Python-mysqldb

2, or PIP installation

Pip Install Mysql-python

3. After installation, import the module

Import MySQLdb

4. Check the database after logging into the database

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| SYS |
+--------------------+
4 rows in Set

5. Create a database

Mysql> CREATE database soms character set UTF8;
Query OK, 1 row affected

6. Create table, table name discovery

CREATE TABLE discovery (ID int (2) NOT NULL PRIMARY key AUTO_INCREMENT,IP varchar (+), port Int (ten), status text) default chars Et=utf8;

7. View Table Structure

mysql> desc Discovery;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| ID | Int (2) | NO | PRI | NULL | auto_increment |
| IP | varchar (40) |     YES | |                NULL | |
| Port | Int (10) |     YES | |                NULL | |
| Status | Text |     YES | |                NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in Set

8, the query table data

Mysql> select * from Discovery;
Empty Set
No data at the moment, it's an empty table.

9, insert a piece of data, and query

mysql> INSERT INTO Discovery (Ip,port,status) VALUES ("192.168.89.3", "True");
Query OK, 1 row affected

Mysql> select * from Discovery;
+----+--------------+------+--------+
| ID | IP | Port | Status |
+----+--------------+------+--------+
| 1 |   192.168.89.3 | 22 | True |
+----+--------------+------+--------+
1 row in Set

10, the database is established, you can use Python through the installed mysqldb to connect the name is called SOMs Library.

Import mysqldbdbhost = "192.168.89.101" DBUSER = "root" dbpasswd = "1qaz#edc" DB = "soms" PORT = 3306CHARSET = "UTF8" conn = MyS Qldb.connect (Host=dbhost, User=dbuser, passwd=dbpasswd, Db=db, Port=port, Charset=charset)

Python establishes a connection to the data, in fact, to create an MySQLdb.connect() instance object, or loosely referred to as the connection object, Python is through the connection object and the database dialog. 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 does not have permission to do it, nothing will happen.

• Rollback (): If you have permission, cancel the current operation, or an error.

• Cursor ([Cursorclass]): Returns the connected Cursor object. Execute the SQL query through the cursor and examine the results. Cursors support more methods than connections, and may be better used in programs.

• Close (): Closes the connection. After that, both the connection object and the cursor are no longer available.

This article is from the "Operation and maintenance of AC Q Group: 223843163" blog, please be sure to keep this source http://freshair.blog.51cto.com/8272891/1903008

Python operation MySQL (i) MYSQLDB module installation and basic database operation