Python Operation MySQL

MySQL Database

A database is a collection of data that is stored in a certain way, shared for multiple users, as redundant as possible, and independent of the application.

There are three types of data on the Earth at this point:

• Relational databases: MySQL, Microsoft Access, SQL Server, Oracle ...
• Non-relational database: MongoDB, BigTable (Google) 、......
• Key values Database: Apache Cassandra (Facebook), LevelDB (Google) ...

Here, just a brief description of how Python connects to the MySQL database and does some simple things.

The Python standard database interface for Python Db-api,python DB-API provides developers with a database application programming excuse.
The Python database interface supports very many databases. You can access the Python database interface and API to view a detailed list of supported databases.

Python's Db-api, which implements the interface for most databases, uses it to connect to each database and manipulate the databases in the same way.
Python DB-API Usage Process:

• Introducing API Modules
• Get a connection to a database
• Execute SQL statements and stored procedures
• To close a database connection

Installing PYTHON-MYSQLDB

PYTHON-MYSQLDB is an interface program that Python uses to implement various operations on MySQL data.

If you want to install the source code, you can download it here: Python-mysqldb

Connect to MySQL Database

ImportMysqldbdb_config={' Host ':' 192.168.189.100 ',#填写主机名 (String)    ' user ':' Root ',#填写登录用户 (String)    ' passwd ':' XXXXXX ',#填写登录密码 (String)    ' DB ':' Test ',#选择数据库 (String)    ' Port ':33066                 #填写端口 (int)    ' CharSet ':' Utf-8 '              #设定字符集}Try: Conn=MySQLdb.Connect(**Db_config)except Exception  asE:RaiseEfinally:Print "MySQL connection succeeded!" "

In fact, there are many parameters about connect, you can read the source code. However, the above several are commonly used, others are used to see the situation.

Python Operations Database

Python establishes a connection to the data, in fact, to create an MySQLdb.connect() instance object, or a general term called a Connection object, python through the Connection object and the database dialog. Common methods for this object are:

• commit(): If the database table has been modified, commit to save the current data. If this user does not have permissions, nothing will happen.
• rollback(): Rollback operation
• cursor([cursorclass]): Returns the connected Cursor object. Execute the SQL query through the cursor and examine the results. Cursors support more methods than connections, and may be better used in programs.
• close(): Closes the connection. After this, the connection objects and cursors are no longer available

PythonAfter the connection with the data is established, to manipulate the database, you need to have python SQL statements executed against the database. pythonis the execution of the SQL statement through the cursor. Therefore, after the connection is established, the cursor object will be obtained using the connection object, as follows:

= conn.cursor()printtype(cur)         # 结果为: <class 'MySQLdb.cursors.Cursor'>

Thereafter, the database can be manipulated using the method of the cursor object. Common methods for cursor objects:

name	Description
Close ()	Closes the cursor, after which the cursor is not available
Execute (Query[,args])	Executes an SQL statement that can take parameters
Executemany (QUERY,PSEQ)	Executes the SQL statement for each parameter in the sequence PSEQ
Fetchone ()	Returns a query result
Fetchmany ([size])	Returns the size bar result
Nextset ()	Move to the next result
Scroll (value,mode= ' relative ')	Moves the cursor to the specified line, if mode= ' relative ', represents the The current row moves the value bar, if mode= ' absolute ', then Represents moving the value bar from the first row of the result set

Inserting data

For example, to insert a record in the data table student, so that: id = 2,name = ' Wangwu ', you can do this

cur.execute("insert into student(id,name) values (%s,%s);",(2,'wangwu'))conn.commit()cur.close()

Python Operation MySQL