Detailed explanation of python link to mysql database

After learning some basic python things, I always want to write a program by myself, but writing a program without a database seems too low-end. So how do I connect python to mysql? Let me give you a detailed introduction

I use the MYSQL database operated by MySQLdb. Let’s take a simple example first:

importMySQLdb
 
try:
    conn=MySQLdb.connect(host='localhost',user='root',passwd='root',db='test',port=3306)
    cur=conn.cursor()
    cur.execute('select * from user')
    cur.close()
    conn.close()
exceptMySQLdb.Error,e:
     print"Mysql Error %d: %s"%(e.args[0], e.args[1])

Please pay attention to modify your database, host name, user name, and password.

Let’s briefly demonstrate examples of inserting data, inserting data in batches, and updating data:

importMySQLdb
 
try:
    conn=MySQLdb.connect(host='localhost',user='root',passwd='root',port=3306)
    cur=conn.cursor()
     
    cur.execute('create database if not exists python')
    conn.select_db('python')
    cur.execute('create table test(id int,info varchar(20))')
     
    value=[1,'hi rollen']
    cur.execute('insert into test values(%s,%s)',value)
     
    values=[]
    fori inrange(20):
        values.append((i,'hi rollen'+str(i)))
         
    cur.executemany('insert into test values(%s,%s)',values)
 
    cur.execute('update test set info="I am rollen" where id=3')
 
    conn.commit()
    cur.close()
    conn.close()
 
exceptMySQLdb.Error,e:
     print"Mysql Error %d: %s"%(e.args[0], e.args[1])

Please note that there must be the sentence conn.commit() to submit the transaction, otherwise the data cannot be truly inserted. .

After running, the results of my MySQL database are no longer as shown in the picture.

importMySQLdb
 
try:
    conn=MySQLdb.connect(host='localhost',user='root',passwd='root',port=3306)
    cur=conn.cursor()
     
    conn.select_db('python')
 
    count=cur.execute('select * from test')
    print'there has %s rows record'%count
 
    result=cur.fetchone()
    printresult
    print'ID: %s info %s'%result
 
    results=cur.fetchmany(5)
    forr inresults:
        printr
 
    print'=='*10
    cur.scroll(0,mode='absolute')
 
    results=cur.fetchall()
    forr inresults:
        printr[1]
     
 
    conn.commit()
    cur.close()
    conn.close()
 
exceptMySQLdb.Error,e:
     print"Mysql Error %d: %s"%(e.args[0], e.args[1])

After querying, the Chinese will be displayed correctly, but it will be garbled in the database. Note that a parameter charset needs to be added here:

Add an attribute to the Python code

conn = MySQLdb.Connect(host='localhost', user='root', passwd='root', db='python'):
Change to:
conn = MySQLdb.Connect(host='localhost', user='root', passwd='root', db='python', charset='utf8')
charset is the encoding of your database Similarly, if the database is gb2312, write charset='gb2312'.

Remarks: python mysql link commonly used functions

commit() commit
rollback() rollback

cursor method used to execute commands:
callproc(self, procname, args): used to execute stored procedures, receive The parameters are the stored procedure name and parameter list, and the return value is the number of affected rows.
execute(self, query, args): Execute a single SQL statement. The parameters received are the SQL statement itself and the parameter list used. The return value is the number of affected rows. The number of rows
executemany(self, query, args): Execute the heads-up SQL statement, but repeatedly execute the parameters in the parameter list, and the return value is the number of affected rows
nextset(self): Move to the next result set

Methods used by cursor to receive return values:
fetchall(self): Receive all returned result rows.
fetchmany(self, size=None): Receive size return result rows. If the value of size is greater than the number of returned result rows , will return cursor.arraysize pieces of data.
fetchone(self): Return a result row.
scroll(self, value, mode='relative'): Move the pointer to a certain row. If mode='relative', it means Move the value bar from the current row. If mode='absolute', it means moving the value bar from the first row of the result set.