[Python] using MySQL

MySQLdb is the package used in Python to manipulate the MYSQL Database

Address:http://sourceforge.net/projects/mysql-python/

MySQLdbinstallation

Under Windows is the EXE version number of the one-click Installation . linux requires manual installation, installation process encountered some problems, here is a brief description of the installation under Linux .

wget http://XXX# Download

Tar zvxf xxx.tar.gz# decompression

CD xxx/# into the catalogue

Python setup.py build# error

Error:can not find Mysql_config

The number of parameters to configure for MySQL .

Find/-name mysql_config# start looking for mysql_config files from the root folder

Found, then edit the previous file site.py:

Mysql_config =/usr/bin/xxx

Not found, to download these two packages:Libmysqld-dev,libmysqlclient-dev

sudo apt-get install Ibmysqld-dev

sudo apt-get install Libmysqlclient-dev

Re-change site.py:

Mysql_config.path= "/usr/bin/mysql_config"

Continue execution:

Python setup.py build# error

Error:command 'gcc'failed with exit status 1

Continue installing these two packages:Build-essential,python-dev

sudo apt-get install build-essential

sudo apt-get install Python-dev

Continue execution:

Python setup.py Build

Python setup.py Install

(Reference Blog: http://blog.163.com/[email protected]/blog/static/1216503422012626105639255/ ）

MySQLdbUse

MySQLdb of the poor document,sourceforge only a simple introduction to English, a lot of features have been tried out by themselves.

English document:http://sourceforge.net/p/mysql-python/mysqldb-2/ci/default/tree/doc/MySQLdb.txt#l61

Simple operation:

Importing packages

Import MySQLdb

Establish a connection

conn = MySQLdb.connect (host= ' 192.168.1.108 ', user= ' MDX ', passwd= ' med ', db= ' mydb ', port=3306, charset= "UTF8")

Creating a Cursor Object

cursor = Conn.cursor ()

Run

sql = ' Select%^*$^&^ '

Cursor.execute (SQL)

Get return information

Results = Cursor.fetchall ()

# The results here is a list, and a row of information in the database is a tuple in the list.

Input in the form of parameters

sql = ' INSERT INTO table1 (ID, name) VALUES (%s%s) '

param = (ten, ' Huang ')

Cursor.execute (SQL, param)

* Note that regardless of the number of parameters, placeholders are%s,param to enable tuples, or arrays

BULK INSERT Data

sql = ' INSERT INTO table1 (ID, name) VALUES (%s%s) '

param = [(Ten, ' Huang '), (p, ' Li ') ...]

Cursor.executemany (SQL, param)

* Here param each element is a tuple, param itself can make tuples, or arrays

Close connection

Cursor.close ()
Conn.close ()

Both cursors and connections are closed

Exporting data

The database that is written on the local machine, to be put on the server side, use import and export, implement this function.

First, the export database on the local machine is in . SQL format:

The ability to use MySQL Workbench graphical interface, but also the command line can be used.

Mysqldump-uroot-p (password)-h127.0.0.1 database1 > Mydb.sql

Get the mydb.sql Export File

Uploading SQL files to server using FTP

Then import the SQL file on the server:

Mysql-uroot-p (password)-h127.0.0.1;

MySQL > CREATE database mydb# creating new Databases

MySQL > Source mydb.sql# import sql file

[Python] using MySQL