How to operate a MySQL database using python

I insist on learning a little bit every day and accumulate a little bit every day. I wrote this article during my dinner and used my spare time to learn how to operate MYSQL in python, so sort it out. I am using a MYSQL database operated by MySQLdb. Let's take a simple example. The code is as follows:

Import MySQLdb

Try:
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'root', db = 'test', port = 3306)
Cur = conn. cursor ()
Cur.exe cute ('select * from user ')
Cur. close ()
Conn. close ()
Counter T MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

Make sure to modify your database, host name, user name, and password.

The following example shows how to insert data, insert data in batches, and update data:

The code is as follows:

Import MySQLdb

Try:
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'root', port = 3306)
Cur = conn. cursor ()

Cur.exe cute ('create database if not exists Python ')
Conn. select_db ('Python ')
Cur.exe cute ('create table test (id int, info varchar (20 ))')

Value = [1, 'Hi rollen']
Cur.exe cute ('Insert into test values (% s, % s) ', value)

Values = []
For I in range (20 ):
Values. append (I, 'Hi rollen' + str (I )))

Cur.exe cute.pdf ('Insert into test values (% s, % s) ', values)

Cur.exe cute ('update test set info = "I am rollen" where id = 3 ')

Conn. commit ()
Cur. close ()
Conn. close ()

Counter T MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

Note that you must have the conn. commit () statement to commit the transaction. Otherwise, you cannot insert data.

The results of my MySQL database cannot be obtained after running.

The code is as follows:

Import MySQLdb

Try:
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'root', port = 3306)
Cur = conn. cursor ()

Conn. select_db ('Python ')

Countw.cur.exe cute ('select * from test ')
Print 'there has % s rows record '% count

Result = cur. fetchone ()
Print result
Print 'Id: % s info % s' % result

Results = cur. fetchtasks (5)
For r in results:
Print r

Print '=' * 10
Cur. scroll (0, mode = 'absolute ')

Results = cur. fetchall ()
For r in results:
Print r [1]


Conn. commit ()
Cur. close ()
Conn. close ()

Counter T MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

The running result will not be pasted, which is too long.

After the query, Chinese characters are correctly displayed, but garbled characters are displayed in the database. After searching through the Internet, I found that a property can be used:

In Python code

Conn = MySQLdb. Connect (host = 'localhost', user = 'root', passwd = 'root', db = 'Python') adds an attribute:
Changed:
Conn = MySQLdb. Connect (host = 'localhost', user = 'root', passwd = 'root', db = 'Python', charset = 'utf8 ')
Charset is the same as the encoding of your database. if the database is gb2312, write charset = 'gb2312 '.

The following describes common functions:

Then, this connection object also provides support for transaction operations, standard methods
Commit () submit
Rollback () rollback

Cursor is used to execute commands:
Callproc (self, procname, args): used to execute a stored procedure. the received parameters are the stored procedure name and parameter list. The Returned values are the number of affected rows.
Execute (self, query, args): executes a single SQL statement. The received parameters are the SQL statement itself and the list of parameters used. The Returned values are the affected rows.
Executemany (self, query, args): executes a single-pick SQL statement, but repeats the parameters in the parameter list. the returned value is the number of affected rows.
Nextset (self): move to the next result set

Cursor is used to receive the returned value:
Fetchall (self): receives all returned result rows.
Fetchmany (self, size = None): Receives the size of returned results rows. if the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.
Fetchone (self): returns a result line.
Scroll (self, value, mode = 'relative '): move the pointer to a row. if mode = 'relative ', the value Bar is moved from the current row. if mode = 'absolute', the value Bar is moved from the first row of the result set.