MySQLdb Simple Query

What is MySQLdb

Simply put, Python is a module or driver that connects to MySQL, and it's easy to use. If you have used other languages, such as Java, then JDBC must be familiar, and MySQLdb is a JDBC-like character in Python.

How to use

Database operation is nothing more than crud operation (Create,retrieve,update,delete), that is, adding and removing changes to check operation. A larger range of speaking is to write and read two operations. Reading data is generally simple, enter the normal SQL statement. But writing data has to consider transactions. Whether it is read or write, the premise is repeatedly on the database can operate, then we will start from the most basic ideas.

Connecting to a database

Import mysqldbconnection = Mdb.connect ("127.0.0.1", 3306, "root", "root", "dbname")

This means that the database is connected.

Execute SQL query statement

Import MySQLdb as Mdbconnection = Mdb.connect ("127.0.0.1", 3306, "root", "root", "test") cursor = Connection.cursor () Cursor.execute ("Select * FROM table") result = Cursor.fetchall () cursor.close () Connection.close ()

Note here that the execution of this method is Cursor.close (), and if you do not execute this statement to close the cursor in the execution of the play SQL statement, you may encounter the following error message:

Commands out of sync; You can ' t run the This command now

The above query to find the information knowledge of the simple output, if you want to output JSON format data with columns need further processing, in fact, not to deal with, because MySQLdb has helped us to think of this, just need in Connection.cursor () Add a parameter mdb.cursors.DictCursor, as follows:

Import MySQLdb as Mdbconnection = Mdb.connect ("127.0.0.1", 3306, "root", "root", "test") cursor = Connection.cursor (   Mdb.cursors.DictCursor) Cursor.execute ("Select * FROM table") result = Cursor.fetchall () cursor.close () Connection.close ()

Execute Write statement

Executing the Write statement is the Insert,update,delete action statement. As for why the write is listed separately, it is because of the thing of the transaction. If you use the above case to execute the write statement, you will find that although the execution display succeeds, but the database does not have any changes, may try the following example:

Import MySQLdb as Mdbconnection = Mdb.connect ("127.0.0.1", 3306, "root", "root", "test") cursor = Connection.cursor (   Mdb.cursors.DictCursor) cursor.execute ("Update table set field= ' xxx '") cursor.commit () Cursor.close () Connection.close ()

So far there should be no problem, if you still can't change the database changes, then you can only read this article

Other operations

• View all databases
  

Import MySQLdb as Mdbconnection = Mdb.connect ("127.0.0.1", 3306, "root", "root") cursor = Connection.cursor ( Mdb.cursors.DictCursor) Cursor.execute ("Show databases") result = Cursor.fetchall () cursor.close () Connection.close ()

• View a table in a library

  Note: do not use dbname; Such a statement, directly in the Connect () method to pass the database name can be

Import MySQLdb as Mdbconnection = Mdb.connect ("127.0.0.1", 3306, "root", "root", "test") cursor = Connection.cursor ( Mdb.cursors.DictCursor) Cursor.execute ("Show tables") result = Cursor.fetchall () cursor.close () Connection.close ()

MySQLdb Simple Query