Data table operations for the Python operations database

Data sheet:

Data type:

Three forms of help:

Enter in CMD: Help to the keyword, or? The subject word to help or the \h to help.

Creation of data tables:

CREATE database IF NOT exists zbltest2 default character set ' UTF8 '; Use Zbltest2; CREATE TABLE IF not EXISTS ' user ' (ID smallint,username VARCHAR ()) Engine=innodb Charset=utf8;

1 #SELECT now (); 2 # SELECT * FROM Student 3--note alter 4 CREATE database IF NOT exists zbltest2 default character set ' UTF8 '; 5 use Zbltest2; 6 CREATE TABLE IF not EXISTS ' user ' (7 ID SMALLINT, 8 username VARCHAR () 9) Engine=innodb charset=utf8;10 11--News category 12 CREATE TABLE IF not EXISTS cms_cate (ID tinyint,14 catename varchar, catedesc varchar) Engine=myisam charse  t=utf8;17 #创建新闻表19 CREATE TABLE IF not EXISTS cms_news (ID int,21 title VARCHAR), content text,23 Pubtime int,24 Clicknum int,25 istop TINYINT (1) COMMENT ' 0 for Pail top, 1 for pinned ' + ' engine=innodb Charset=utf8;

Insert:

The following SQL command inserts data into student:

1 CREATE database IF NOT exists zbltest1 default character set ' UTF8 '; 2 use Zbltest1; 3 #下面的 ' student ' is an anti-quote, in the number 1 left and the wavy line on a key, the purpose is to not and SQL comes with the keyword Coincident 4 CREATE TABLE IF not EXISTS ' student ' (5 ID INT, 6 ' name ' VARCHAR ( ), 7 gender VARCHAR (8) Engine=innodb Charset=utf8; 9 Insert Student VALUES (1, ' ZBL1 ', ' Man '), insert student values (2, ' ZBL2 ', ' Mans '), insert student values (3, ' ZBL3 ', ' "Man"); select * from student;

The data structure can be seen through MySQL Workbench.

Below, connect and manipulate MySQL via python:

1 Import pymysql 2 try:3 # Get a database connection, note if the UTF-8 type, you need to develop a database of 4     conn=pymysql.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' zbltest1 ', port=3306,charset= ' UTF8 ') 5     cur=conn.cursor () #获取一个游标 6     cur.execute ("INSERT Student VALUES (' 4 ', ' Zbl4 ', ' man ') ') 7     cur.execute (' select * FROM student ') 8     # Data=cur.fetchall () 9 for     D i n cur:10         #注意int类型需要使用str函数转义12         print ("ID:" +str (d[0]) + '  name: ' +d[1]+ "  Gender:" +d[2] "         print (" Row_number: ", (Cur.rownumber))         #print (' hello ')     cur.close () #关闭游标17     conn.close () #释放数据库资源18 Except  Exception:p rint ("Exception occurred")

Running the following Python code requires you to first install the Pymysql module, which you can enter in the command line (that is, cmd): Pip Install Pymysql

Haha, finally connected, Python is through the function to the database management system to pass SQL statements to manipulate the database.

Note that Python has to use the 3.4,mysql 5.7.