Shulou(Shulou.com)06/02 Report--

This article mainly explains "what are the operation methods of Python database in Mysql automation operation and maintenance development", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the operation methods of Python database in the development of Mysql automation operation and maintenance"?

Classification of 1.MYSQL language

(1) DDL database definition

(2) DQL database query

(3) DML database operation

(4) DCL database permissions

2.MYSQL operation

(1) create a database

Mysql > create database cmdb default charset utf8

(2) View all databases

Mysql > show databases;+-+ | Database | +-+ | information_schema | | cmdb | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.00 sec)

(3) use cmdb database

Mysql > use cmdb

(4) View the database creation syntax

Mysql > show create database cmdb

(5) Delete the database

Mysql > drop database cmdb

(6) View all tables

Mysql > show tables

(7) create a user table

Mysql > create table user (id int,name varchar 64, age int, sex boolean,telphone varchar 32, addr varchar 512) engine=innodb default charset utf8

(8) View the process of creating a table

Mysql > show create table user

(9) Delete tables

Mysql > drop table user

(10) View the table structure

Mysql > desc user

(11) insert data

Mysql > insert into user (id,name,age,sex,telphone,addr) values (1)'Li Kuan', 25'Li Kuan', '18829787559' Xi'an, Shaanxi Province')

(12) View data

Mysql > select * from user

(13) query only specified columns

Mysql > select name,addr from user

(14) conditional inquiry

Where

Logical correlative word and or

Relational expression >

= select name,age,addr from user where addr ='Xi'an City, Shaanxi Province'or age = 25

Select name,age,addr from user where addr ='Xi'an City, Shaanxi Province'or age > 25

Mysql > select name,age,addr from user where age > = 25

Mysql > select name,age,addr from user where age! = 25

Select name,age,addr from user where age

Select name,age,addr from user where addr like 'Shaanxi Province%'

Mysql > select name,age,addr from user where addr like'% City'

Mysql > select name,age,addr from user where not (addr like 'Linfen')

Mysql > select name,age,addr from user where age in (235.25)

Mysql > select name,sex,age,addr from user where age not in (155.25)

(15) Total number of enquiries

Mysql > select count (*) from user

3. Create a user table for CMDB

The sql of the table. When the gender is stored in the database, the male deposit is 1 and the female deposit is 0.

CREATE TABLE user (id int primary key auto_increment, name varchar (32) unique not null default'', password varchar (512) not null default'', age int not null default 18, sex boolean not null default 1, tel varchar (16) not null default', addr text, add_time datetime) ENGINE=INNODB DEFAULT CHARSET utf8mb4

Insert test data in bulk

Insert into user (name, password, age, sex, tel, addr, add_time) values ('kk', md5 (' kk'), 30,1, '15200000000000000),' Xi'an', now ()),\ ('woniu', md5 (' woniu'), 30,1, '152000000001', 'Beijing', now ()), ('zhangzhengguang', md5 (' zhangzhengguang'), 30,1, '15200000003million, Hangzhou', now ()),\ ('likuan' ()) Md5 ('likuan'), 30, 1,' 15200000002, Xi'an, now ()

View the user name and password for the user to log in

Mysql > select name,password from user where name='likuan' and password=md5 ('likuan')

Find all the data

Mysql > select id,name,password,age,sex,tel,addr from user

Limit the data queried (limit can be used for paging)

Mysql > select id,name,password,age,sex,tel,addr from user limit 1

Limit and offset are used together

Mysql > select id,name,password,age,sex,tel,addr from user limit 2 offset 2

Sort (descending and ascending)

Descending order (desc)

Mysql > select id,name,password,age,sex,tel,addr from user order by age desc

Ascending order (asc)

Mysql > select id,name,password,age,sex,tel,addr from user order by age asc

Update operation

Mysql > update user set age=15 where id = 3; mysql > update user set name='kk',tel='152',sex=1,addr=' Xi'an 'where id = 1

Delete operation

Mysql > delete from user where id = 1 * * MySQL > delete from user

Aggregate function

Mysql > select max (age), min (age), avg (age), count (age), sum (age) from user

Classified statistics

Mysql > select addr, count (*) from user group by addr

Mysql > select addr,age, count (*) from user group by addr,age

Manipulate mysql in 4.Python code

First of all, you need to install mysql's development package mysql-devel

Secondly, pip installs mysqlclient

Use is the import package MysqlSQLdb

Seven steps for Python to operate mysql

(1) Import module

Import MySQLdb

(2) create a connection

Conn=MySQLdb.connect (host='127.0.0.1',port=3306,user='root',passwd='passwd',db='cmdb')

(3) get cursors

Cursor = conn.cursor ()

(4) execute sql (DQL and DML)

DQL

Returns the number that meets the criteria

Cursor.execute ("select id,name from user where name='likuan' and password=md5 ('likuan');")

DML

Cursor.execute ("update user set age = 35 where id = 1")

(5) DQL obtains the result and DML submits it for execution

DQL (tuple)

Cursor.fetchall () cursor.fetchone () > > cursor.fetchall () (('kk',), (' likuan',), ('woniu',), (' zhangzhengguang',)

DML submission

Conn.commit ()

(6) close the cursor

Cursor.close ()

(7) close the connection

Conn.close ()

5. Submit sql by preprocessing (prevent sql injection)

(1) separate operation from data

(2) two variables, one is the sql operation and the other is the corresponding data

(3) only data can occupy space, and operation can not occupy space.

At this point, I believe that everyone has a deeper understanding of "what are the operation methods of Python database in the development of Mysql automation operation and maintenance". You might as well come to the actual operation. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.