How to connect python to a mysql database

After learning some basic python things, I always want to write a program by myself, but writing a program does not use a database, which is too low-end. how can I operate a python link to mysql? The following is a detailed introduction for everyone... after learning some basic python things, I always want to write a program by myself, but writing a program does not use a database, which is too low-end. how can I operate a python link to mysql? The following is a detailed introduction for everyone.

I am using a MYSQL database operated by MySQLdb. Let's take a simple example:

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])

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:

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])

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.

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 the query, Chinese characters are correctly displayed, but garbled characters are displayed in the database. Note that a charset parameter must be added here:

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 '.

Note: common functions linked to python mysql

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.