[Python] using MYSQL

MYSQLdb is the package address used to operate MYSQL Databases in Python: sourceforge. netprojectsmysql-pythonMYSQLdb is a one-click installation EXE version in windows. Manual installation is required in Linux. Some problems are encountered during the installation process. Here we will briefly describe the installation of. wgetXXX # in Linux.

MYSQLdb is Python used to operate MYSQL database package address: http://sourceforge.net/projects/mysql-python/ MYSQLdb installation windows is a one-click installation of EXE version. Manual installation is required in Linux. Some problems are encountered during the installation process. Here we will briefly describe the installation of. wgethttp: // XXX # in Linux.

MYSQLdb is the package used to operate MYSQL Databases in Python.

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

Install MYSQLdb

In windows, the EXE version is installed with one click. Manual installation is required in Linux, and some problems are encountered during the installation process. Here we will briefly describe the installation in Linux.

Wget http: // XXX # download

Tar zvxf XXX.tar.gz # decompress

Cd XXX/# enter the folder

Python setup. py build # error

Error: can not find mysql_config

Configure mysql parameters.

Find/-name mysql_config # search for the mysql_config file from the root directory

Find, edit the previous file site. py:

Mysql_config =/usr/bin/XXX

Cannot be found. You need to download these two packages: libmysqld-dev and libmysqlclient-dev.

Sudo apt-get install ibmysqld-dev

Sudo apt-get install libmysqlclient-dev

Modify site. py again:

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

Continue running:

Python setup. py build # error

Error: command 'gcc 'failed with exit status 1

Continue to install these two packages: build-essential, python-dev

Sudo apt-get install build-essential

Sudo apt-get install python-dev

Continue running:

Python setup. py build

Python setup. py install

(Refer to blog: http://blog.163.com/rettar@126/blog/static/1216503422012626105639255)

Use MYSQLdb

The documentation for MYSQLdb is poor. There is only one introduction to sourceforge in English, and I have tried all the functions myself.

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

Simple operation:

Import package

Import MYSQLdb

Establish a connection

Conn = MySQLdb. connect (host = '2017. 168.1.108 ', user = 'mdx', passwd = 'dd', db = 'mydb', port = 3306, charset = "utf8 ")

Create a cursor object

Cursor = conn. cursor ()

Run

SQL = 'select % ^ * $ ^ & ^'

Cursor.exe cute (SQL)

Get Response Information

Results = cursor. fetchall ()

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

Parameter format input

SQL = 'insert into table1 (id, name) values (% s )'

Param = (10, 'huangt ')

Cursor.exe cute (SQL, param)

* Note: whatever the parameter, the placeholder value is % s. param can make tuples or arrays.

Batch insert data

SQL = 'insert into table1 (id, name) values (% s )'

Param = [(10, 'huangt'), (20, 'lil')...]

Cursor.exe cute.pdf (SQL, param)

* Here, each element of param is a tuples. param itself can use tuples or arrays.

Close connection

Cursor. close ()
Conn. close ()

Close both the cursor and connection.

Export data

The database written on the local machine should be placed on the server side and imported and exported to implement this function.

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

You can use the graphical interface of mysql workbench or the command line interface.

Mysqldump-uroot-p (password)-h127.0.0.1 database1> mydb. SQL

Obtain the exported mydb. SQL file.

Use FTP to upload SQL files to the server

Then import the SQL file on the server:

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

Mysql> create database mydb # create a new database

Mysql> source mydb. SQL # import SQL files