Python database operations-data tables, python Databases

Source: Internet
Author: User

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.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.