Python database operations-data tables, python Databases
Data Table:
Data Type:
Three forms of help:
Enter the following keywords in cmd: help, or? The key word or the key word that \ h wants to help.
Create a data table:
CREATE database IF NOT exists zbltest2 default character set 'utf8';USE zbltest2;CREATE TABLE IF NOT EXISTS `user`(id SMALLINT,username VARCHAR(20)) ENGINE=INNODB CHARSET=UTF8;
1 # select now (); 2 # SELECT * FROM student 3 -- Comment 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 (20) 9) ENGINE = innodb charset = UTF8; 10 11 -- news classification 12 create table if not exists cms_cate (13 id TINYINT, 14 cateName varchar (50), 15 cateDesc varchar (200) 16) ENGINE = MyISAM CHARSET = UTF8; 17 18 # CREATE news TABLE 19 create table if not exists cms_news (20 id INT, 21 title VARCHAR (50), 22 content TEXT, 23 pubTime INT, 24 clickNum INT, 25 isTop TINYINT (1) COMMENT '0 indicates that the parameter is not set to top, and 1 indicates that the parameter is set to top '26) ENGINE = innodb charset = UTF8;
Insert:
Run the SQL command to insert data to student:
1 CREATE database if not exists zbltest1 default character set 'utf8'; 2 USE zbltest1; 3 # the 'Student 'Below is a reverse quotation mark, and the left side of number 1 and the wavy line are on a single button, the purpose is NOT to overlap with the SQL built-in keywords 4 CREATE TABLE IF NOT EXISTS 'student '(5 id INT, 6 'name' VARCHAR (45), 7 gender VARCHAR (45) 8) ENGINE = innodb charset = UTF8; 9 INSERT student values (1, 'zbl1', 'man'); 10 INSERT student values (2, 'zbl2', 'man '); 11 INSERT student values (3, 'zbl3', 'man'); 12 select * FROM student;
You can see the data structure through mysql workbench.
Connect to and manipulate mysql using python:
1 import pymysql 2 try: 3 # Get A database connection, note that if it is UTF-8 type, need to develop database 4 conn = pymysql. connect (host = 'localhost', user = 'root', passwd = 'root', db = 'zbltest1', port = 3306, charset = 'utf8 ') 5 cur = conn. cursor () # Get a cursor 6 cur.exe cute ("INSERT student VALUES ('4', 'zbl4', 'man ')") 7 cur.exe cute ('select * from student ') 8 # data = cur. fetchall () 9 for d in cur: 10 11 # note that the int type must use the str function to escape 12 print ("ID:" + str (d [0]) + 'Name: '+ d [1] + "Gender:" + d [2]) 13 print ("row_number:", (cur. rownumber) 14 # print ('hello') 15 16 cur. close () # close the cursor 17 conn. close () # release database resources 18 TB t Exception: print ("Exception occurred ")
To run the following python code, you must first install the pymysql module. You can enter pip install pymysql in the command line (in cmd ).
Haha, finally connected. python uses functions to pass SQL statements to the database management system to manipulate the database.
Note that python uses 3.4 and mysql uses 5.7.