Python operation MySQL

First, MySQL basic operation

1. Set root password/ change root password

Method One: With Mysqladmin

mysqladmin-u root Password "Newpass"

　　　　　　mysqladmin-u root password Oldpass "Newpass"

Method Two: Use the Set password command (can also be used to change the password)

Mysql-u Root

mysql> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Newpass ');

mysql> FLUSH privileges;

Method Three: Edit the user table directly with update (can also be used to modify the password)

Mysql-u Root

mysql> UPDATE mysql.user SET Password = Password (' newpass ') WHERE user = ' root ';

mysql> FLUSH privileges;

2. Crack root Password

Mysqld_safe--skip-grant-tables&

mysql> UPDATE mysql.user SET password=password ("Newpass") WHERE user= ' root ';

mysql> FLUSH privileges;

3, increase and revise the search　

mysql> show databases;　　　　# View all databases mysql> CREATE database S12day9 charset ' UTF8 '; # Create a database named S12day9 and set the character encoding to UTF8
mysql> use S12day9; # Switch to the S12DAY9 database
Mysql> CREATE TABLE Students # Creates a students table with primary key ID and cannot be null---ID int NOT NULL auto_i Ncrement primary key, name char (+) NOT NULL, sex char (a) not null, age tinyint UN　　　　　　　　　　　　　　　　　　Signed NOT NULL, tel char (+) NULL default "-");mysql> desc students;　　　　　　　　　　 # View Students table Structure mysql> show create table students;  　　# View Table Detail Structure Statement mysql> INSERT into students (Name,sex,age,tel) VALUES (' Alex ', ' Boy ', 18, ' 151515151 '); # Insert a data mysql> insert into students (Name,sex,age,tel) VALUES (' Flash ', ' Man ', ' a ', ' 156515151 ');mysql> insert INTO　　　　　　　　　　　　　　　　Students (Name,sex,age,tel) VALUES (' Tony ', ' Man ', ' Max ', ' 196515151 ');mysql> select * from students where age> 20;　　　　　　# Query The students table for data records older than 20 mysql> select * from students where ages > and sex = ' man ';　　　　　　　　　　　　Mysql> SELECT * from students where the age is like ' 2% '; Mysql> Select Name,sex from students where the age like ' 2% '; mysql>　　　　　　　　　　Update students set age =-where name= ' Alex '; # Update Students table in Alex age of mysql> select * from students where ages like ' 2% ';mysql> update students set　　　　　　　　　　　　　　　　　　　　 26;　　　　　　　　　　　　　　 # Set The age of everyone is 26mysql> delete from students where name= ' Tony ';　　　　　　　　　　　# Delete the data record named Tony mysql> ALTER TABLE Students add column nal char (64); # Add a nal field

　

Second, python operation MySQL

Python2 operation MySQL generally use the mysqldb module, MYSQLDB does not seem to support Python3, so all the following code is implemented in Python2.

The MySQLdb module can be obtained through Yum installation Mysql-python in the CentOS system, or mysqldb can be obtained through PIP installation umysqldb.

　　　a) yum install Mysql-python directly into import mysqldb

　　　b) pip install Umysqldb

>>> Import Umysqldb

>>> Umysqldb.install_as_mysqldb ()

>>> Import MySQLdb

　1. Inserting data

#!/usr/bin/env python2# Encoding:utf-8import mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 123 ,. ABC ', db= ' s12day9 ') cur = conn.cursor () recount = Cur.execute (' INSERT into students (id,name,sex,age,tel,nal) values (%s ,%s,%s,%s,%s,%s) ', (4, ' Eric ', ' Oldboy ', ' + ', ' 159595959 ', ' 1234 ')) Conn.commit () Cur.close () conn.close () print recount

　　BULK INSERT Data

1 #!/usr/bin/env Python22 #Encoding:utf-83 4 ImportUmysqldb5 umysqldb.install_as_mysqldb ()6 ImportMySQLdb7 8conn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='123,.ABC', db='S12day9')9 TenCur =conn.cursor () One  ALi = [ -(15,'Rambo','Superman',' -','18995959595','9527'), -(16,'Rambo','Superman',' -','18995959595','9527'), the(17,'Rambo','Superman',' -','18995959595','9527'), - ] -recount = Cur.executemany ('INSERT into students (id,name,sex,age,tel,nal) VALUES (%s,%s,%s,%s,%s,%s)', Li) -  + Conn.commit () -  + cur.close () A conn.close () at  - PrintRecount

View Code

　2. Query data

　　　　Fetchone

#!/usr/bin/env python# encoding:utf-8import mysqldb  conn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 123,.abc ', db= ' s12day9 ') cur = conn.cursor ()   recount = Cur.execute (' SELECT * from students ')   print Cur.fetchone ()       # prints the 1th data print     cur.fetchone ()       # prints 2nd data    cur.scroll ( -1,mode= ' relative ')       # Sets the cursor to the relative position, Move relative up 1print cur.fetchone ()                     # Prints the 2nd data Print cur.fetchone ()                     # Print 3rd Data cur.scroll (0,mode= ' absolute ')      # Set cursor to absolute position, scale 0print Cur.fetchone ()                     # print 1th data print cur.fetchone ()                     # prints 2nd data  

　　　　Fetchall

Import Mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 123,.abc ', db= ' s12day9 ') cur = conn.cursor ( ) recount = Cur.execute (' Select Name,age from students ') Nret = Cur.fetchall () cur.close () conn.close () print nretfor i in nRe T:    print i[0],i[1]

　　3. Modify the data

Import Mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 123,.abc ', db= ' s12day9 ') cur = conn.cursor ( ) #reCount = Cur.execute (' update students set name =%s ', (' Alin ',)) recount = Cur.execute (' update students set name =%s WH Ere ID > ', (' Rambo ',)) Conn.commit () Cur.close () conn.close () print recount

4. Delete Data

Import Mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 123,.abc ', db= ' s12day9 ') cur = conn.cursor ( ) recount = Cur.execute (' Delete from students where name =%s ', (' Alin ',)) Conn.commit () Cur.close () conn.close () print Recount

　　

　　

Python operation MySQL