Python connection database operation MySQL

Referenc

Http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/001391435131816c6a377e100ec4d43b3fc9145f3bb8056000

Http://www.cnblogs.com/fnng/p/3565912.html

One module

Mysql-connector-python: MySQL's official pure Python driver

MySQL-python: a Python driver that encapsulates the MySQL C driver.

You can install both and decide which one to use when you use them:

$pip install mysql-connector-pythonpip install MySQL-python

Or use yum installation (I forgot the package name can be found by using yum search mysql)

Yum install MySQL-python

Test whether to follow a good driver

#! / usr/bin/env python#coding:utf-8import systry: import MySQLdb print "MySQL python drivier is ok!" except Exception, e: print e sys.exit (1) finally: sys.exit (1)

Tables in the database

Show create table net_data; can view

CREATE TABLE `net_ data` (`_ id` int (11) NOT NULL AUTO_INCREMENT, `create_ date` datetime NOT NULL, `province` varchar (64) NOT NULL, `city` varchar (64) NOT NULL, `net_ type` enum ('CTC','CNC','CMC','JK') NOT NULL, `med` float NOT NULL, `loss` float NOT NULL, PRIMARY KEY (` _ id`) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

Third, let's take a look at the insertion example first.

Function, connect to the database and insert a piece of data

Insert a piece of data into the database

Import MySQLdb.def insert_into_mysql (info): conn= MySQLdb.Connect (host='localhost', port = 3306, user='root', passwd='12qwaszx', db = 'netmap', charset='utf8',) cur = conn.cursor () sqli = "insert into net_data (create_date,province,city,net_type) Med,loss) values (% cur.close% SJM% s) "cur.execute (sqli, (datetime.now (), 'Shanghai', 'CTC','23','0')) conn.commit () conn.close ()

Insert multiple pieces of data at the same time

# info type is list list def insert_into_mysql (info): conn= MySQLdb.Connect (host='localhost', port = 3306, user='root', passwd='12qwaszx', db = 'netmap', charset='utf8',) cur = conn.cursor () sqli = "insert into net_data (create_date,province,city,net_type,med) Loss) values (% sqli% s info% s) "cur.executemany (sqli, (info)) conn.commit () cur.close () conn.close ()

Query examples (complete part of the program, complete a program for e-mails sent by statistical reports)

#! / usr/bin/env python#encoding=utf8#by cuizhiliang at 2016-04-29from datetime import datetime, timedeltaimport timefrom jinja2 import Templatefrom sendmail import SendHtmlEmailimport MySQLdbimport os,sysreload (sys) sys.setdefaultencoding ("utf-8") g = {'host':' 127.0.0.1, 'port': 3306,' user': 'zabbix',' password': 'zabbix',' db':'zabbix' } now= datetime.now () yesterday = now-timedelta (1) yest_clock = (datetime.now ()-timedelta (1)). Strftime ('% s') data = {} def select_mysql (cmd): conn= MySQLdb.Connect (host= g ['host'], port= g [' port'], user= g ['user'], passwd= g [' password'], db = g ['db'] Charset='utf8',) # print cmd cur = conn.cursor () cur.execute (cmd) res = cur.fetchall () return res#zabbix user list def get_user_info (): cmd= "select userid, alias, name, type from users "user_list= [] user_list= list (select_mysql (cmd)) print user_list return user_listif _ _ name__ =" _ _ main__ ": data [" user "] = get_user_info ()

5. Note:

1 the character set for inserting garbled connection data in the database must be set.

Charset='utf8'

2 not accustomed to the field in the database is datetime type or int type, when inserting are passed as% s string type, the type is transparent to us, the module will automatically deal with the type!

3 Modules may make errors in mac MySQLdb

Error report:

Traceback (most recent call last):

File "ping_info.py", line 12, in

Import MySQLdb

File "/ Library/Python/2.7/site-packages/MySQLdb/__init__.py", line 19, in

Import _ mysql

ImportError: dlopen (/ Library/Python/2.7/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib

Referenced from: / Library/Python/2.7/site-packages/_mysql.so

Reason: p_w_picpath not found

Solution, because it is C language development driver, all rely on mysql client module

Sudo find /-name "libmysqlclient.18.dylib"

/ usr/local/mysql-5.6.24-osx10.8-x86_64/lib/libmysqlclient.18.dylib

Sudo cp / usr/local/mysql-5.6.24-osx10.8-x86_64/lib/libmysqlclient.18.dylib / usr/lib