Python2.0_s12_day9_mysql operation

Basic syntax for MySQL:
1. Database operations
show databases;
Create database name, if you want to allow the database to write Chinese create database name CharSet UTF8
Use database name;
Show tables;

2. Data Sheet operation
CREATE TABLE Table name
(
ID int NOT NULL auto_increment primary key, # Primary key refers to a unique identifier for each row
Name Char (9) is not NULL,
Sex char (4) NOT NULL,
Age tinyint unsigned not NULL, # unsigned
Tel char (+) NULL default "_"
);
DESC table name; View table structure
Show create table table name; See what statement This table was created from
ALTER TABLE Students add column field name char (30); Insert a field to a table
InnoDB data Engine, is to support transactional operations, such as ATM bank transfer, cash transfer, when you cash deposit, the start of the transfer of power, then the transfer failed, and the database will be credited to the success of the record will be rolled back, become unsuccessful, and then the money back to you.

3. Data manipulation
Insert into table name (Field 1, Field 2, Field 3) VALUES (' Value 1 ', ' Value 2 ', ' Value 3 '); Data insertion
Delete from table name where field 1 = ' value '; Delete Row Records
Update table name set field 2 = ' sb ' where field 1 = ' value '; Update a field value for a record in a table
SELECT * from table name; Lookup table All records

4. Other
Primary key
FOREIGN key
Left and right connections

Python-connected MySQL module

Python has many modules connected to MySQL, we use the MYSQLDB module and need to download it.
First, insert data

1 Import MySQLdb2 3conn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='1234', db='MyDB')4 5Cur =conn.cursor ()6 7recount = Cur.execute ('INSERT INTO UserInfo (name,address) VALUES (%s,%s)',('Alex','USA'))8# recount = Cur.execute ('INSERT INTO UserInfo (name,address) values (% (ID) s,% (Name) s)',{'ID':12345,'name':'Wupeiqi'})9 Ten Conn.commit () One  A cur.close () - conn.close () -  thePrint recount

    The above uses the Cur.execute () method to insert a record, then how to bulk Insert data records. You can use Cur.executemany ()

1 Import MySQLdb2 3conn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='1234', db='MyDB')4 5Cur =conn.cursor ()6 7Li =[8('Alex','USA'),9('SB','USA'),Ten         ] Onerecount = Cur.executemany ('INSERT INTO UserInfo (name,address) VALUES (%s,%s)', Li) A  - Conn.commit () - cur.close () the conn.close () -  -Print recount

    Ii. deletion of data

1 Import MySQLdb2 3conn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='1234', db='MyDB')4 5Cur =conn.cursor ()6 7recount = Cur.execute ('Delete from UserInfo')8 9 Conn.commit ()Ten  One cur.close () A conn.close () -  -Print recount

    Third, modify the data

1 Import MySQLdb2 3conn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='1234', db='MyDB')4 5Cur =conn.cursor ()6 7recount = Cur.execute ('Update UserInfo Set Name =%s',('Alin',))8 9 Conn.commit ()Ten cur.close () One conn.close () A  -Print recount

    Iv. Data Search

1# ############################## fetchone/fetchmany (num) ##############################2 3 Import MySQLdb4 5conn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='1234', db='MyDB')6Cur =conn.cursor ()7 8recount = Cur.execute ('SELECT * from UserInfo')9 Ten print Cur.fetchone () One print Cur.fetchone () ACur.scroll (-1, mode='relative') - print Cur.fetchone () - print Cur.fetchone () theCur.scroll (0, mode='Absolute') - print Cur.fetchone () - print Cur.fetchone () -  + cur.close () - conn.close () +  APrint recount

1 # ############################## Fetchall ##############################2 3 Import MySQLdb4 5conn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='1234', db='MyDB')6#cur = conn.cursor (Cursorclass =MySQLdb.cursors.DictCursor)7Cur =conn.cursor ()8 9recount = Cur.execute ('Select name,address from UserInfo')Ten  OneNret =Cur.fetchall () A  - cur.close () - conn.close () the  - Print Recount - Print Nret -          forIinchNret: +Print i[0],i[1]

Python2.0_s12_day9_mysql operation