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

1. Python operates the database

1. Format: roughly divided into three parts

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

Code

Import MySQLdb

Conn = MySQLdb.connect (host='192.168.0.180',user='cattle',passwd='cattle',db='cattle')

Cur = conn.cursor () # create a connection

ReCount = cur.execute ('select * from admin')

Data = cur.fetchall () # manipulate the data

Cur.close () # close the connection

Conn.close ()

Print data

Print reCount # this means the number of messages affected by the execution of this command

Result

((1L,'N2, 'a2'), (2L,' N1, 'a1'))

two

1. Establishment and release of connections

The connect function is available when establishing a connection, which returns an object of type connection

one

Db= MySQLdb.connect (host='192.168.0.180',user='cattle',passwd='cattle',db='cattle')

Parameters commonly used in connect:

Host: database host name. The default is to use local host

User: database login name. The default is the current user

Passwd: the secret of database login. Default is empty

Db: the name of the database to use. No default value

The TCP port used by the port:MySQL service. The default is 3306

Charset: database coding

If you insert garbled data into the database when the data encoding is set correctly, you can set the character set parameters of the connection

You can use the close method of an object of type connection when releasing a connection

one

Conn.close ()

2.cursor object

Before executing the SQL statement, you need to get a cursor object with a specified connection, and the cursor object executes the SQL query and gets the result.

The method of getting the cursor object

one

Cur = conn.cursor ()

By default, the cursor method returns an object of type BaseCursor, and the result of each record of an object of type BaseCursor is represented as a list (list) after the query is executed. If you want to return the record represented by the dictionary (dict), set the cursorclass parameter to the MySQLdb.cursors.DictCursor class

one

Cur = conn.cursor (cursorclass=MySQLdb.cursors.DictCursor)

3. Insert, delete, update, query and other operations

The cursor type provides an execute method for executing SQL statements

3.1 query

one

Cur.execute ('select * from admin')

3.2 get the result

There are three ways to get the result: fetchone, fetchall, and fetchmany. The returned result is a record in the query result corresponding to each element in the tuple,tuple.

Fetchone () returns the first record in the result set

Fetchall () returns all records in the result set

Fetchmany ([size]) returns size records in the result set

3.3 insert

Because SQL statements are long, SQL statements can be defined as variables

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

Import MySQLdb

Conn = MySQLdb.connect (host='192.168.0.180',user='cattle',passwd='cattle',db='cattle')

Cur = conn.cursor ()

Sql = "insert into admin (name,address) values (% s)" # name and address are placeholders equivalent to key,%s

Params = ('n4) # n4 and A4 are equivalent to value, written in the placeholder position

ReCount = cur.execute (sql,params)

Conn.commit () # after performing the actions of adding, deleting or changing, you must perform this step to submit before it takes effect.

Cur.close ()

Conn.close ()

Print reCount

3.4 Delete

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

Import MySQLdb

Conn = MySQLdb.connect (host='192.168.0.180',user='cattle',passwd='cattle',db='cattle')

Cur = conn.cursor ()

Sql = "delete from admin where id =% s"

Params = (1)

ReCount = cur.execute (sql,params)

Conn.commit ()

Cur.close ()

Conn.close ()

Print reCount

3.5 Chan

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

Import MySQLdb

Conn = MySQLdb.connect (host='192.168.0.180',user='cattle',passwd='cattle',db='cattle')

Cur = conn.cursor ()

Sql = "update admin set name =% s where id = 8"

Params = ('N8')

ReCount = cur.execute (sql,params)

Conn.commit ()

Cur.close ()

Conn.close ()

Print reCount

4. Business

When python operates the database, once there is an error and does not submit the operation, it will only be submitted when there is no problem.

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.