Python link MySQL and common syntax

MySQL is a relational database management system, its small size, speed, the total cost of ownership is low, especially the open source of this feature, the general development of small and medium-sized web sites have chosen MySQL as the site database. It is not always used to deal with it, which results in the use of a specific syntax. Below for everyone to summarize some of the commonly used (although often the back of the slip ~)

In the python3 using mysql first import pymysql module, without this module can be PIP installation, if not MySQL,, reference HTTP// www.cnblogs.com/xsmile/p/7753984.html for installation.

Enter MySQL to see what the current database contains:

Mysql-h localhost-u Root-p Enter the password into the MySQL environment.

Show Databses; #显示当前数据库列表

Show tables; #显示当前数据库中包含的所有表

Show CREATE TABLE table_name; # View Table Structure

ALTER TABLE Old_name Rename to New_name #修改数据表名

ALTER TABLE table_name Rename column old_column_name to New_column_name #修改列名

ALTER TALE table_name MODIFY COLUMN_NAME New_type # Modifying the data type of a column

ALTER TABLE table_name ADD COLUMN_NAME datatype # Insert Column

ALTER RABLE table_name DROP COLUMN column_name delete columns

To operate the database in a DOS window, select a database before you can manipulate the tables in it. i.e. use db_name;

I take the Blog_test table operation in the test database as an example:

1 mysql> use test; 2 Database changed 3 mysql> select * from blog_test;

After the screen output:

+----------+-----------------+--------+| Username | Email           | Gender |+----------+-----------------+--------+| Charles  | [Email protected] | Male   | | Jerry    | [email protected]    | Female | | Xsmile   | [email protected]  | Male in    Set (0.03 sec)

Of course the key to the operation of MySQL in Python is basically divided into:

1, import pymysql; 2, link to a database, 3, create a cursor, 4, the syntax to manipulate.

To create a table in the database:

1 ImportPymysql #导入包2Db=pymysql.connect (host='localhost', user='Root', passwd='123456', db='Test') #链接数据库3Cursor=db.cursor () #创建游标, manipulating the table4Cursor.execute ("drop table if EXISTS blog_test") #如存在blog_test表, delete the5Cursor.execute ("CREATE TABLE Blog_test (6 username char (+),7 email CHAR () not NULL,8 Gender CHAR (6) not NULL,9 PRIMARY key (username)) Charset=utf8") #charset =utf8 make the table support Chinese, otherwise the default is Latin1Ten cursor.close () One Db.commit () ADb.close ()

It's useless to create an empty table, it has the content to be valuable, only use the meaning AH.

Data is inserted into the database.

1 cursor.execute ("insert INTO blog_test (Username,email,gender) VALUES (' Xsmile ', ' [email Protected] ', ' male ')

Very vivid statement, and MySQL in the syntax is almost the same. After learning to sqlalchemy This module will be much more convenient ~, the database is inserted in multiple statements with multiple INSERT statements. Here we have a concise method in the code that creates a list of the content to be inserted and completes multiple insertions.

1ins="INSERT INTO Blog_test VALUES (%s,%s,%s)"2datas=[('Charles','[email protected]','male'),('Jerry','[email protected]','female'),]3Cursor.executemany (Ins,datas)

Insert here is: Cursor.executemany (), but there is a small hint, below the db.commit () can be implemented into the table. Otherwise, there is no error, but cannot be inserted into the table.

Make a query to see the data in the table first

1 ImportPymysql #导入模块2Db=pymysql.connect (host='localhost', user='Root', passwd='123456', db='Test') #链接到数据库3Cursor=db.cursor () #创建游标对表进行相关操作4Li=cursor.execute ("SELECT * from Blog_test") #对表进行查询5 Print(LI)6 cursor.close () #关闭游标7Db.close () #断开数据库

At this point we find out that the output is a number 3, what is the case, the View database table is blog_test see there are 3 data. So, what do we want to show the 3 data?!

Of course it's looping, and in turn, the tuple output in the table.

1 info=cursor.fetchmany (li)2 for in info:3      Print(item)

There is also Cursor.fetchone (), which indicates that a piece of data is taken from it. There is also Cursor.fetchall ()when the tuple is taken, no parameters are required, and the data obtained is the entire data at the cursor 's current position to the end of the data. You can add a where condition when you make a conditional query, and the statement is the same as the method in MySQL.

The above mentioned have query and insert, then look at the change operation.

1 cursor.execute ("update blog_test set username= ' Sofia ' where email= ' [email protected] ' ")

Note the use of quotation marks in the query field cannot be missing.

Delete operation if everyone knows, put the MySQL statement in Cursor.execute (), you can complete the relevant operation. Then we'll delete the row of data that we just changed.

1 cursor.execute ("delete from blog_test where username= ' Sofia '")

After you query the Blog_test table, the data information named Sofia is deleted. Of course, to delete the entire table, you use the drop statement, think twice about this operation, or you will delete the library to flee the person ~

Deleting and opening transactions and rolling back

In the Insert, change, delete (delete) operation, the transaction can be opened in advance , so that when the error or return is only rollback back to the original data (the transaction is opened by default in Pycharme, But finally there is a commit (), so that after the implementation of the program is also irreparable. For example:

1  mysql> select * from B; #查看b表数据2+------+3| B |4+------+5| 4 |6| 5 |7| 6 |8| 7 |9+------+Ten4 rowsinchSet (0.00sec) One  AMysql>begin; #开启事务 -Query OK, 0 rows affected (0.00sec) -  theMysql>Delete from B where b=5; #把为5的元组删除 -Query OK, 1 row affected (0.05sec) -  -Mysql>SELECT * from B; ++------+ -| B | ++------+ A| 4 | at| 6 | -| 7 | -+------+ -3 RowsinchSet (0.00sec) -  -Mysql>rollback; #回滚 inQuery OK, 0 rows affected (0.11sec) -  toMysql>SELECT * from B; ++------+ -| B | the+------+ *| 4 | $| 5 |Panax Notoginseng| 6 | -| 7 | the+------+ +4 rowsinchSet (0.00 sec)

But drop(delete the table directly, there is no way back), and truncate(delete all the data in the table, cannot be used with where) the rollback will not save the deleted data .

Originality is not easy, respect copyright. Reprint Please specify source:http://www.cnblogs.com/xsmile/

Python link MySQL and common syntax