Python operation mysql (1) MySQLdb module installation and basic database operation
1, Install python-MySQLdbsudo apt-get install build-essential python-dev libmysqlclient-devsudo apt-get install python-MySQLdb under ubuntu environment
2, or PIP installation
pip install mysql-python
3. Import the module after installation
import MySQLdb
4. Log in to the database and view 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 a table named discovery
create table discovery(id int(2) not null primary key auto_increment,ip varchar(40),port int(10),status text)default charset=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. Query the data in the table
mysql> select * from discovery;
Empty set
There is no data at present. It is an empty table.
9. Insert a piece of data and query
mysql> insert into discovery(ip,port,status) values("192.168.89.3",22,"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
After the database is established, you can use python to connect this library called soms through the installed mysqldb.
import MySQLdbDBHOST = "192.168.89.101"DBUSER = "root"DBPASSWD ="1qaz#EDC"DB = "soms"PORT = 3306CHARSET = "utf8"conn = MySQLdb.connect(host=DBHOST, user=DBUSER, passwd=DBPASSWD, db=DB, port=PORT, charset=CHARSET)
Python establishes a connection to the data, in fact, it establishes an instance object of MySQLdb.connect(), or generally called a connection object, python is to talk to the database through the connection object. Common methods for this object are:
commit(): Commit to save the current data if the database table has been modified. Of course, if this user doesn't have permission, nothing happens.
rollback(): If you have permission, cancel the current operation, otherwise an error is reported.
cursor([cursorclass]): Returns the cursor object of the connection. Execute SQL queries through cursors and examine the results. Cursors support more methods than connections and are probably better used in programs.
close(): Close the connection. After that, both the connection object and cursor are no longer available.
