Quick familiarity with using mysql (MySQLdb) in python)

Quick familiarity with using mysql (MySQLdb) python in python

First, you need to install mysql and MySQLdb modules (other modules can be used, of course). here I will skip this step. if you encounter any problems, Baidu (or comment below, I can help you)

Here is a simple record of your learning process:

1. connect to the database

MySQLdb provides the connect function, using the following

　　cxn=MySQLdb.Connect(host='localhost',user='root',passwd='',db='samp_db',port=3306)

The parameter meanings here are clear, but these parameters are not all necessary:

1. the host parameter indicates the address of the database. the default value is localhost. that is to say, the local machine can run this parameter without specifying

2. the user name and password of the user and passwd database must exist.

3. select the database name you want to operate on the database. this can be specified later, not required

4. port number. the default value is 3306.

5. charset is used to specify the character set (utf8 by default)

II. database operations

1. some objects can directly use the query (but it is not recommended to use it (so it is skipped here). even if it is used, you must first determine whether this attribute exists)

Cxn. query ('SQL statement ')

2. use cur

　　cur=cxn.cursor()

In this way, we can use cur to execute various operations. the sample code is as follows:

1 import MySQLdb2 cxn=MySQLdb.Connect(host='localhost',user='root',passwd='',db='samp_db',port=3306)3 cur=cxn.cursor()4 result=cur.execute('select * from students')5 for i in cur.fetchall():6 print i

This code will return all the information in the students table, that is, the content displayed after you enter mysql to input select * from students. Here I assume that everyone knows how to use SQL statements (if not, you can learn or use ORM to operate databases)

Next we will demonstrate how to update a table and how to insert data into the table as an example:

 1 import MySQLdb 2 cxn=MySQLdb.Connect(host='localhost',user='root',passwd='',db='samp_db',port=3306) 3 cur=cxn.cursor() 4 def showtables(tname): 5 result=cur.execute('select * from %s'%tname) 6 for i in cur.fetchall(): 7 print i 8 showtables('students') 9 cur.execute("insert into students values(4,'liu4',100)")10 print 'after insert'11 showtables('students')

In this way, we can view the results:

(1L, 'liu', Decimal('100'))(2L, 'liu2', Decimal('90'))(3L, 'liu3', Decimal('98'))after insert(1L, 'liu', Decimal('100'))(2L, 'liu2', Decimal('90'))(3L, 'liu3', Decimal('98'))(4L, 'liu4', Decimal('100'))

It seems that we have done the same job, but at this time we use a terminal to connect to mysql. After executing a select * from students statement, we found that the inserted result was not included. This is because we still lack a commit job. In addition, after the work is completed, we need to close the connection to the database, so the change code is as follows:

 1 import MySQLdb 2 cxn=MySQLdb.Connect(host='localhost',user='root',passwd='',db='samp_db',port=3306) 3 cur=cxn.cursor() 4 def showtables(tname): 5 result=cur.execute('select * from %s'%tname) 6 for i in cur.fetchall(): 7 print i 8 showtables('students') 9 cur.execute("insert into students values(4,'liu4',100)")10 cxn.commit()11 print 'after insert'12 showtables('students')13 cur.close()14 cxn.close()

3. obtain the return value

In the preceding example, the query results are stored in a result variable, and tuple is returned. However, cursor has several methods: 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.

IV. batch execution and parameters

Using MySQLdb, we can not only execute a statement, but also combine it with python so that we can perform batch operations.

 1 import MySQLdb 2 cxn=MySQLdb.Connect(host='localhost',user='root',passwd='',db='samp_db',port=3306) 3 cur=cxn.cursor() 4 def showtables(tname): 5 result=cur.execute('select * from %s'%tname) 6 for i in cur.fetchall(): 7 print i 8 showtables('students') 9 v=[]10 for i in range(10):11 v.append((i,'liu'+str(i),98))12 cur.executemany("insert into students values(%s,%s,%s)",v)13 cxn.commit()14 print 'after insert'15 showtables('students')16 cur.close()17 cxn.close()

The preceding two parameters are used, Line 5 and line 12th.

In addition, when executing multiple commands, use executetasks (op, args), which is similar to the combination of execute () and map (), to prepare and execute a database query/command for each given parameter.

V. fragmentation

As mentioned above, the db attribute can be specified after the connection, as shown below:

cxn.select_db('students')