Python operations database-data tables

Tag: Release com LTE cat char test rownumber cmd number

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 existsZbltest2default character Set 'UTF8'; UseZbltest2;CREATE TABLE IF  not EXISTS`User' (IDSMALLINT, usernameVARCHAR( -) ) ENGINE=INNODB CHARSET=UTF8;

1#SELECTNow ();2#SELECT *  fromStudent3 --Note Alter4 CREATE Database IF  not existsZbltest2default character Set 'UTF8';5  UseZbltest2;6 CREATE TABLE IF  not EXISTS`User`(7IdSMALLINT,8UsernameVARCHAR( -)9) ENGINE=INNODB CHARSET=UTF8;Ten  One --News Category A CREATE TABLE IF  not EXISTSCms_cate ( -IdTINYINT, -Catenamevarchar( -), theCatedescvarchar( $) -) ENGINE=MyISAM CHARSET=UTF8; -  - #创建新闻表 + CREATE TABLE IF  not EXISTSCms_news ( -IdINT, +TitleVARCHAR( -), AContentTEXT, atPubtimeINT, -ClicknumINT, -IstopTINYINT(1) COMMENT'0 stands for Pail Top, 1 for pinned' -) ENGINE=INNODB CHARSET=UTF8;

Insert:

The following SQL command inserts data into student:

1CREATE database IF NOT exists zbltest1 default character set'UTF8';2 Use zbltest1;3 #the following ' student ' is an anti-quote, on the left side of the number 1 and the wavy line on a key, in order to not coincide with the SQL self-brought keyword4 CREATE TABLE IF not EXISTS ' student ' (5 ID INT,6' Name ' VARCHAR (45),7Gender VARCHAR (45)8) Engine=innodb charset=UTF8;9INSERT Student VALUES (1,'ZBL1','Mans');TenINSERT Student VALUES (2,'ZBL2','Mans'); OneINSERT Student VALUES (3,'ZBL3','Mans'); ASELECT * from student;

The data structure can be seen through MySQL Workbench.

Below, connect and manipulate MySQL via python:

1 ImportPymysql2 Try:3 #get a database connection, note if the UTF-8 type, you need to make a database4Conn=pymysql.connect (host='localhost', user='Root', passwd='Root', db='Zbltest1', port=3306,charset='UTF8')5Cur=conn.cursor ()#gets a cursor6Cur.execute ("INSERT Student VALUES (' 4 ', ' Zbl4 ', ' Man ')")7Cur.execute ('SELECT * FROM Student')8     #Data=cur.fetchall ()9      forDinchcur:Ten  One         #note the int type needs to be escaped with the STR function A         Print("ID:"+str (D[0]) +'Name:'+d[1]+"Gender:"+d[2]) -         Print("Row_number:", (cur.rownumber)) -         #print (' Hello ') the  -Cur.close ()#Close Cursors -Conn.close ()#Releasing database Resources - exceptException:Print("An 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.

Python operations database-data tables