Python MysqlDb module installation and Usage Details, pythonmysqldb

Python MysqlDb module installation and Usage Details, pythonmysqldb

Python uses the mysqldb module to call mysql databases.

1. Install the driver

Currently, there are two MySQL drivers. You can choose one of them for installation:

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

2. mysql-connector-python: it is the official pure Python driver of MySQL.

The MySQL-python driver is used here, that is, the MySQLdb module.

Command line installation

pip install python-mysql

Or install it in the pycharm package.

Source code Installation Method

Access: http://www.lfd.uci.edu /~ Gohlke/pythonlibs/, MySQL_python-1.2.5-cp27-none-win_amd64.whl download

Copy it to the Scripts directory under the Python installation directory, open cmd at the file location, execute pip install MySQL_python-1.2.5-cp27-none-win_amd64.whl

Verify that python (command line) Inputs import MySQLdb. If no error is reported, the installation is successful.

Test connection:

#! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # connect to the database connection address account password database code db = MySQLdb. connect ("localhost", "root", "123456", "test", charset = "utf8") # Use the cursor () method to obtain the operation cursor = db. cursor () # Use the execute method to execute the SQL statement cursor.exe cute ("SELECT VERSION ()") # Use the fetchone () method to obtain a database. Data = cursor. fetchone () print "Database version: % s" % data # close Database connection db. close ()

Example 1:

#!/usr/bin/python # coding=utf-8 import MySQLdb import os, sys import json class MysqlDb(object):    def __init__(self):     self.host = "127.0.0.1"    @staticmethod   def get_connect():     db = MySQLdb.connect(self.host , "mail_report", "mail_report", "mailawst", charset="utf8")     return db    def get_mysql_info(self,start_time,end_time):     tmp = []     db = self.get_connect()     sql = 'select send_time,mail_id,mail_addr,server_domain,server_ip,mail_status from real_mail_log where send_time > "%s" and send_time < "%s" limit 10;' % (start_time,end_time)     cursor = db.cursor()     cursor.execute(sql)     values = cursor.fetchall()     for i in values:       data = {}       data["send_time"] = str(i[0])       data["mail_id"] = str(i[1])       data["mail_addr"]= str(i[2])       data["server_domain"] = str(i[3])       data["server_ip"] = str(i[4])       data["mail_status"]= str(i[5].encode('utf8'))         tmp.append(data)     data = json.dumps(tmp,ensure_ascii=False)     db.close()     return data  def main():   u = MysqlDb()   print u.get_mysql_info('2017-05-01 00:00:02','2017-05-01 00:50:03')  if __name__ == '__main__':   main() 

Example 2:

#! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "root", "123456", "test") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL insert statement ins_ SQL = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('yu ', 'jie', 20, 'M', 8000) "" ins_sql1 = 'insert into employee (first_name, last_name, age, sex, income) values (% s, % s) '# SQL query statement sel_ SQL = 'select * from employee where first_name = % s' # SQL update statement upd_ SQL = 'Update employee set age = % s where sex = % s' # SQL delete statement del_ SQL = 'delete from employee where first_name = % s' try: # Run the SQL statement # insert cursor.exe cute (ins_ SQL) cursor.exe cute (ins_sql1, ('xu ', 'F', 20, 'M', 8000 )) # select cursor.exe cute (sel_ SQL, ('yu ',) values = cursor. fetchall () print values # update cursor.exe cute (upd_ SQL, (24, 'M',) # delete cursor.exe cute (del_ SQL, ('xu ',)) # submit to the database for execution db. commit () commit T: # roll back the db when an error occurs. rollback () # disable database connection to db. close ()

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.