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

Python operation MySQL

-query operation of MySQL

-insert data of MySQL

-batch insert data of MySQL

-deleted data of MySQL

-updating the database of MySQL

-MySQL business

-batch acquisition of dictionary type data by MySQL

-fetchone of MySQL

-the acquisition of MySQL self-increasing ID

Three-tier architecture (the program is divided into three architectures)

-Common layer of three-tier architecture

-model layer of three-tier architecture

-configuration file for three-tier architecture

Socket network programming

[Python operation MySQL]

Preparatory work:

Windows download link, Python operation MySQL module:

Http://pan.baidu.com/s/1o7JuMgU

Tip: after installing the mysql module in Windows, because I am using Eclipse, I also need to make some settings under Eclipse, as follows:

Click Windows- > Preferences- > PyDev-Interpreter-Python- > Forced Builtins- > New- > MySQLdb- > ok- > Apply on the Eclipse menu bar, and restart Eclipse if necessary

Linux directly installs Python to operate MySQL module yum-y install python-mysqldb

Construction table sentence

Create table students (id int not null auto_increment primary key, name char (8) not null, sex char (4) not null, age tinyint unsigned not null, tel char (13) null default "-")

Insert data:

Insert into students (name,sex,age,tel) values ('wsyht','man',20,'1319')

Database and table

Mysql > select database (); +-+ | database () | +-+ | wsyht | +-+ 1 row in set (0.00 sec) mysql > select * from students +-+ | id | name | sex | age | tel | +-- + | 2 | wsyht | man | 22 | 1231313 | | 3 | jack | man | 23 | 12313 | 4 | jenkins | man | 25 | 123 | | | 5 | peter | man | 23 | 23 | 6 | wsyht90 | man | 23 | 23 | 8 | wsyht11 | man | 26 | 12345 | 9 | wsyht12 | man | 26 | 12345 | 10 | wsyht1 | man | 26 | 12345 | 11 | wsyht2 | man | 26 | 12345 | +-+ 9 rows in set (0.00 sec)

1) query operation of MySQL

Import MySQLdb # must install the python operation mysql module before importing the MySQLdb module

# create a connection

Conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as holding out a hand, here it can be understood as reaching out a hand to get the table data

# data query operation

ReCount = cur.execute ('select * from students') # then performs the action, only querying how many rows are affected, not querying the data of related tables data = cur.fetchall () # take out all the rows affected by the select query

# close the connection

Cur.close () # take back conn.close () # close the door

# output information

Print reCount # output the number of rows affected by the query print data # output data for the rows affected by the query

2) insert data of MySQL

Import MySQLdb

# create a connection

Conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as holding out a hand, here it can be understood as reaching out a hand to get the table data

# data insertion operation

Sql = "insert into students (id,name,sex,age,tel) values (% s id,name,sex,age,tel% s)" # No matter what type, the placeholder is given to% s. If there are five placeholders, give five% sparams = ('6cm, peterless, etc.) # if there is a self-increment in id here Then the first place in this row and the first place in the row above id can not write reCount = cur.execute (sql,params) # insert data conn.commit () # submit data, insert,update,delete needs to add commit, but select does not

# close the connection

Cur.close () # recall conn.close () # close the door print reCount # the number of lines affected by the output

3) batch insert data of MySQL

Before import MySQLdb # must enter the MySQLdb module, install the python operation mysql module.

# create a connection

Conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as holding out a hand, here it can be understood as reaching out a hand to get the table data

# batch insert data operation

Li = [('wsyht11','man','26','12345'), (' wsyht12','man','26','12345'),] # sql = 'insert into students (name,sex,age,tel) values (% spene% s Magee% s)' # reCount = cur.executemany (sql,li) reCount = cur.executemany ('insert into students (name,sex,age,tel) values (% sMagna% sMagna% s)' Li) # Delete data conn.commit () # submit data Insert,update,delete needs commit, but select does not.

# close the connection

Cur.close () # recall conn.close () # close the door print reCount # the number of lines affected by the output

4) deletion of database by MySQL

Before import MySQLdb # must enter the MySQLdb module, install the python operation mysql module.

# create a connection

Conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as holding out a hand, here it can be understood as reaching out a hand to get the table data

# data deletion operation

Sql = "delete from students where id=%s" # No matter what type, the placeholder is given to% s params = (7,) # add the data content that% s needs to delete to the params variable. If it is a character, it needs to be enclosed in single quotation marks, such as: ('N1,) reCount = cur.execute (sql,params) # Delete data conn.commit () # submit data, insert,update,delete needs to add commit, but select does not need to

# close the connection

Cur.close () # recall conn.close () # close the door print reCount # the number of lines affected by the output

5) update the database of MySQL

Before import MySQLdb # must enter the MySQLdb module, install the python operation mysql module.

# create a connection

Conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as holding out a hand, here it can be understood as reaching out a hand to get the table data

# data update operation

Sql = "update students set name=%s where id=6" # No matter what type, the placeholder is given to% wsyht90' params = ('wsyht90') # change the name content in the row where id is 6 to wsyht90reCount = cur.execute (sql,params) # update data conn.commit () # submit data, insert,update,delete needs to add commit, but select does not

# close the connection

Cur.close () # recall conn.close () # close the door print reCount # the number of lines affected by the output

6) transactions, which will not be executed until all commits are successful

Mysql > select * from students;+----+ | id | name | sex | age | tel | +-- + | 2 | wsyht | man | 22 | 1231313 | | 3 | jack | man | 23 | 12313 |

Import MySQLdb # must enter the MySQLdb module before To install the python operation mysql module conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as reaching out the hand sql = "update students set age=%s where id=2" params = (0) reCount = cur.execute (sql,params) sql = "update students set age=%s where id=3" params = (46) reCount = cur.execute (sql Params) conn.commit () cur.close () # recall conn.close () # close print reCount # the number of lines affected by the output

Mysql > select * from students;+----+ | id | name | sex | age | tel | +-- + | 2 | wsyht | man | 0 | 1231313 | | 3 | jack | man | 46 | 12313 |

7) batch acquisition of dictionary type data by MySQL

Before import MySQLdb # must enter the MySQLdb module, install the python operation mysql module.

# create a link

Conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door # cur = conn.cursor () # here it can be understood as reaching out the hand to get the table data cur = conn.cursor (cursorclass = MySQLdb.cursors.DictCursor) # get the data by dictionary type, that is, get the column names and column data of the table

# data manipulation

ReCount = cur.execute ('select * from students') # then performs the action, only querying how many rows are affected, not querying the data of related tables data = cur.fetchall () # take out all the rows affected by the select query

# close the connection

Cur.close () # retrieve conn.close () # close the door print reCount # output the number of rows affected by the query print data # output the data of the rows affected by the query

8) fetchone of MySQL

Import MySQLdbconn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as reaching out your hand to get the table data # cur = conn.cursor (cursorclass = MySQLdb.cursors.DictCursor)

# data manipulation

ReCount = cur.execute ('select * from students') # then performs the action, only querying how many rows are affected, but not querying the data of the related table data = cur.fetchone () # only outputs the first row of the data, print data # outputs the data of the rows affected by the query # cur.scroll (0lighting affected rows) # sense alignment mode, go up one level From the re-output data = cur.fetchone () # output to the second line of the data print data # output the data of the row affected by the query cur.scroll (- 1 referenced relative') # relative mode, go up one level, from the re-output data = cur.fetchone () # output to the third line of data cur.close () conn.close () print data # output the data of the row affected by the query

9) the acquisition of MySQL self-increasing ID

# you can see that the following table ID is 12, and then I insert the data three times will become 15, the code is as follows

Mysql > select * from students +-+ | id | name | sex | age | tel | +-+ | 3 | jack | man | 22 | 12313 | | 12 | peter | man | 23 | +-+ |

Import MySQLdbconn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # here it can be understood as the key to open the door cur = conn.cursor () # here it can be understood as reaching out your hand to get the table data

# data manipulation

Sql = "insert into students (name,sex,age,tel) values (% spene% sforce% s)" # No matter what type, the placeholder is given to% s. If there are four placeholders, give four% sparams = ('peter','man','23','123') reCount = cur.execute (sql,params) # insert data conn.commit () # submit data, and add commit to insert,update,delete. On the other hand, select does not use new_id = cur.lastrowid # auto-add IDprint new_id # to output a new ID# to close the connection cur.close () # take back conn.close () # close the door

# View the datasheet again

Mysql > select * from students +-+ | id | name | sex | age | tel | +-- + | 3 | jack | man | 22 | 12313 | | 12 | peter | man | 23 | | 13 | peter | man | 23 | 14 | peter | man | 23 | 123 | | | 15 | peter | man | 23 | 123 | +-- + |

[three-tier architecture] (the program is divided into three architectures)

Data access layer

Business processing layer

Presentation layer, UI layer

There are several tables in the 08day10 # project (project)-model # (package) database. Just create a few files here, and correspond to the name of the database one by one, that is, create a module file with the name of whatever table you want to operate on-- _ _ init__.py-students.py # has a table called students. You can call the sql_helper module file to add, delete, modify and check the students table. The public configuration is written in sql_helper. The additions, deletions, changes, and queries are written in students, and finally admin does the execution-utility # (package) common layer, where some common functions are written, such as the operation on the database, and for example, which address you need to request when you want to use the network service, then you can create a separate file for him and unify the operation, all of which are performed by me. The following is the common configuration operation of the _ _ init__.py-sql_helper.py # database-conf.py # (module). For example, the database to be connected or the interface to be connected put his URL here-index.py # (module) main file, the file that the program first executes, index then starts to trigger other classes of other modules, or methods in other classes.

1) Common layer of three-tier architecture

Cat sqlhammer helper.pyknotes pound python#coding:utf-8import MySQLdbimport confclass MySQLHelper (object): def _ _ init__ (self): self.__conn_dict = conf.conf_dict def Get_Dict (self,sql,params): conn = MySQLdb.connect (host='192.168.1.113',user='root',passwd='123456',db='wsyht') cur = conn.cursor () reCount = cur.execute (sql) Params) data = cur.fetchall () cur.close () conn.close () return data # returns a value to the caller def Get_One (self,sql,params): conn = MySQLdb.connect (* * self.__conn_dict) # two * pass multiple values in a dictionary A * passes the value as a list, passing multiple values cur = conn.cursor () reCounts = cur.execute (sql,params) data = cur.fetchone () cur.close () conn.close () return data # returns the value to the caller Note: the call is not written directly in this file This is just to demonstrate helper = MySQLHelper () sql = "select * from students where id >% s" params = (15) one_data = helper.Get_one (sql, params) dict_data = helper.Get_Dict (sql, params) print one_dataprint dict_data'''

2) model layer of three-tier architecture

Cat students.pypostures python#coding:utf-8from utility.sql_helper import MySQLHelper python#coding:utf-8from utility.sql_helper import MySQLHelper # import classes into class Students (object): def _ init__ (self): self.__helper = MySQLHelper () # object-oriented assignment to private fields def Get_One (self) Id): sql = "select * from students where id =% s" params = (id) return self.__helper.Get_One (sql,params) def CheckValidate (self,username,password): sql = "select * from students where name =% s and password =% s" params = (username,password) return self.__helper.Get_One (sql) Params) 3) the configuration file conf of the three-tier architecture is generally set up with the utility layer #! / usr/bin/env python#coding:utf-8# is passed in a dictionary, and the first and second rows are passed in a dictionary You can choose one of them. If you want to change IP or something later, you can change it here conf_dict = dict (host='192.168.1.113',user='root',passwd='123456',db='wsyht') # conf_dict = {'host':'192.168.1.113','user':'root','passwd':'123456','db':'wsyht'}.

# main program execution file

Cat index.pyknox.pyknoxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.PYRUBING BINV python#coding:utf-8from model.students import Studentsdef main (): user = raw_input ('username:') pwd = raw_input (' password:') students = Students () result = students.CheckValidate (user) Pwd) if not result: print 'the user name or password you entered is incorrect' else: print 'Welcome to the background management page' if _ _ name__ ='_ _ main__': main ()

# students table under the wsyht library

Mysql > select * from students +-+ | id | name | sex | age | password | +-- + | 1 | wsyht | man | 18 | 123456 | | 2 | jenkins | man | 20 | 13579 | +-+ -+

Execution sequence summary:-- > 1. Call configuration file

Main program (index file)-- > model layer (students table file)-- > Common layer (sql_helper database common operation file)

-- > 2. Call the database

Summary: the rule of writing is from back to top in the order of execution.

1. Write the common layer first

2. Write model layer

3. Write the main program file

4. Write configuration file

[Socket network programming]

-main package-client.py-server.py example 1: Python#coding:utf-8import socketdef handle_request Python#coding:utf-8import socketdef handle_request (client): buf = client.recv (1024) # client receives server data, cache area 1024 bytes You can only take a maximum of 1024 bytes of client.send ("HTTP/1.1 200 OK\ r\ n\ r\ n\ n") # server send message client.send ("HelloMagneWorldword 123") # server send message print bufdef main (): sock = socket.socket () # establish socket object sock.bind (('localhost') 5821)) # listening on local port 8080 sock.listen (5) # maximum number of connections allowed to be established while True: connection, address = sock.accept () # blocking direct client requests Connectin is the socket object of the client, and address represents the address of the client handle_request (connection) connection.close () if _ _ name__ = ='_ main__': main () # browser access test http://localhost:5821

Example 2:

Main # package-init.py-server.py-client.py server script cat server.py #! / usr/bin/env python#coding:utf-8import socketsk = socket.socket () # calls this class object of socket Create object ip_port = ('127.0.0.1) sk.bind (ip_port) # IP and port sk.listen of the listening server (5) # the maximum number of connections allowed is 5while True: # create an endless loop that allows him to keep receiving requests from users conn,address = sk.accept () # blocking and waiting until a client connection is available Conn is the socket object of the client. Address represents the address of the client, conn.send ('HelloMagneWo2ldlegs') # sends data to the client, conn.close () # closes the connection to the client

Client script

Cat client.pyknoxamusrbinxambinv python#coding:utf-8import socketclient = socket.socket () # create client socketip_port = ('127.0.0.1) client.connect (ip_port) # connect server data = client.recv (1024) # receive server data print data

Execute script: execute server script first, then client script

The socket server performs the following steps:

1. Create a socket object

2. Listen on the server IP and port

3. Set the maximum number of connections

4. Block and wait until there is a client connection

5. Send data

6. Close the connection

Socket client executes steps

1. Create a socket object

2. Establish a connection with the server

3. Request data

Example of interaction between Socket client and server:

-

Main package-client.py-server.py server side demo: #! / usr/bin/env Python#coding:utf-8import socketsk = socket.socket () ip_port = ('127.0.0.1) sk.bind (ip_port) sk.listen (5) # number of blocks while True: conn Address = sk.accept () conn.send ('hello') print' the new user enters the system 'print' server:hello' flag = True while flag: data = conn.recv (1024) # receives client data if data = = 'exit': flag = False print' the other party has exited the system' Break print 'client:' Data inp = raw_input ('server:') conn.send (inp) conn.close ()

Client demo:

#! / usr/bin/env Python#coding:utf-8import socketclient = socket.socket () ip_port = ('127.0.0.1) client.connect (ip_port) while True: data = client.recv (1024) # receiver server data print' server:',data inp = raw_input ('client:') client.send (inp) if inp =' exit': break

# Asynchronous multithreaded server

Cat server.pyram hello' hello' binv Python#coding:utf-8import SocketServerclass MyServer (SocketServer.BaseRequestHandler): def setup (self): passdef handle (self): conn = self.request conn.send ('hello') print' new users enter the system 'print' server:hello' flag = True while flag: data = conn.recv (1024) if data = = 'exit': flag = False print 'the other party has exited the system' break print 'client:' Data inp = raw_input ('server:') conn.send (inp) conn.close () def finish (self): passif _ _ name__ = =' _ main__': server = SocketServer.ThreadingTCPServer ('127.0.0.1) server.serve_forever ()

# client

Cat client.pyknosis while True while True: data = client.recv (1024) print 'server:',data inp = raw_input (' client:') client.send (inp) if inp = = 'exit': break

# you can copy one more client.py file, and then, first execute the server program, and then execute two client files to achieve asynchronous processing of two conversations, that is, asynchronous multithreading

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.