python的describe怎么存为表格_python生成数据库中所有表的DESC描述

#!/usr/ali/bin/python#coding=utf-8

'''Implements a simple database interface

Example 0: Create connection:

# Set auto commit to false, default case

db = DB(auto_commit = False, host = 'x', user = 'x', passwd = 'x', db = 'x')

# Set auto commit to true

db = DB(auto_commit = True, host = 'x', user = 'x', passwd = 'x', db = 'x')

# Set auto connect to true, this will set auto commit to true too

# This will enable auto connect when the connection is timeout

db = DB(auto_connect = True, host = 'x', user = 'x', passwd = 'x', db = 'x')

Example 1: Query SQL

a. Use query() method to execute query sql directly:

# The query() method will get the result rows immediately

db.query('select * from ip', size = 2, is_dict = True)

c. Use split_query() method to split long query into small ones:

# Assume that the name_list's length is 10000

# See the docstring of split_query() for more details

db.split_query('select address from ip', 'name', name_list)

Example 2: Insert SQL

a. Insert a new record into ip table:

db.execute("insert into ip('address','name') values('192.168.0.1','vm-xxx')")

# If auto commit set to false, call commit() method manually

db.commit()

b. Insert multi-records into ip table:

db.executemany("insert into ip('address','name') values(%s,%s)", [

('192.168.0.1', 'vm-xxx'),

('192.168.0.2', 'vm-yyy'),

('192.168.0.3', 'vm-zzz')])

db.commit()

Note: db.multi_insert is an alias for executemany method.'''

#Can be 'Prototype', 'Development', 'Product'

__status__ = 'Development'

__author__ = 'tuantuan.lv '

importreimporttimeimportMySQLdbfrom storage importStorage

OperationalError=MySQLdb.OperationalErrordef_format(sql):'''Format the sql.'''

return ' '.join(sql.split())classDB():'''A simple database query interface.'''

def __init__(self, auto_commit = False, auto_connect =False,

max_idle_time= 28800, **kwargs):'''Initialize the DB object.'''

# #Remember the max idle time (default: 28800)

#You should set this value to mysql option 'wait_timeout'

# #mysql> show variables like 'wait_timeout';

#+---------------+-------+

#| Variable_name | Value |

#+---------------+-------+

#| wait_timeout | 28800 |

#+---------------+-------+

# self.max_idle_time =max_idle_time

kwargs.setdefault('charset', 'utf8') #set default charset to utf8

kwargs['port'] = int(kwargs.get('port', '3306')) #set default port to 3306

self._db= None #MySQLdb connection object

self._db_cursor = None #MySQLdb cursor object

self.cursor = None #MySQLdb cursor object, deprecated

self._db_args = kwargs #MySQL db connection args

self._last_use_time = time.time() #Last active time

self._auto_connect= auto_connect #Auto connect when timeout

self._auto_commit = auto_commit #Auto commit

#Open a new mysql connection

self._reconnect()def __del__(self):

self.close()defclose(self):'''Close the database connection.'''

if self._db is notNone:

self._db_cursor.close()

self._db.close()

self._db=Nonedef_reconnect(self):'''Close existing connection and re-open a new one.'''self.close()

self._db= MySQLdb.connect(**self._db_args)#Override auto commit setting if auto connect is true

ifself._auto_connect:

self._db.autocommit(True)else:

self._db.autocommit(self._auto_commit)

self._db_cursor=self._db.cursor()

self.cursor=self._db_cursordef_ensure_connected(self):'''Ensure we connect to mysql.'''

#Mysql by default closes client connections that are idle for

#8 hours, but the client library does not report this fact until

#you try to perform a query and it fails. Protect against this

#case by preemptively closing and reopening the connection

#if it has been idle for too long (8 hours by default).

if (self._db is None or(time.time()- self._last_use_time >self.max_idle_time)):

self._reconnect()

self._last_use_time=time.time()def_cursor(self):'''Get the cursor.'''

ifself._auto_connect:

self._ensure_connected()returnself._db_cursordef execute(self, sql, args =None):'''Execute a sql and return the affected row number.

You should call the get_rows method to fetch the rows manually.'''cursor=self._cursor()returncursor.execute(_format(sql), args)def execute_lastrowid(self, sql, args =None):'''Execute a sql and return the last row id.

You should call the get_rows method to fetch the rows manually.'''cursor=self._cursor()

cursor.execute(_format(sql), args)returncursor.lastrowiddefexecutemany(self, sql, args):'''Execute a multi-row insert.

You can use this method to do a multi-row insert:

c.executemany(

"""INSERT INTO breakfast (name, spam, eggs, sausage, price)

VALUES (%s, %s, %s, %s, %s)""",

[

("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),

("Not So Much Spam Plate", 3, 2, 0, 3.95 ),

("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )

] )

See http://mysql-python.sourceforge.net/MySQLdb.html for more help.'''cursor=self._cursor()returncursor.executemany(_format(sql), args)#Execute a multi-row insert, the same as executemany()

multi_insert =executemanydef get_rows(self, size = None, is_dict =False):'''Get the result rows after executing.'''cursor=self._cursor()

description=cursor.descriptionif size isNone:

rows=cursor.fetchall()else:

rows=cursor.fetchmany(size)if rows isNone:

rows=[]ifis_dict:

dict_rows=[]

dict_keys= [ r[0] for r indescription ]for row inrows:

dict_rows.append(Storage(zip(dict_keys, row)))

rows=dict_rowsreturnlist(rows)def query(self, sql, args = None, size = None, is_dict =False):'''Execute a query sql and return the rows immediately.'''self.execute(sql, args)returnself.get_rows(size, is_dict)#Alias of query() method

select =querydef split_query(self, sql, in_attr, in_list, max_cnt = 3000):'''Split one long query into many small ones.

For example, if you want to select the records whose attrname is in

one long list (larger than 8000) of possible values. If you decide to

use 'attr in (...)' syntax, the length will exceed the maximum length

of one sql allowed. In this case you must split the long query into many

small ones.

in_attr is the attribute name of in operator, and in_list is the possible

value list. max_cnt is the maximum count of values in one small query.'''total=len(in_list)

start=0

end=max_cnt

result=[]if re.search(r'\bwhere\b', sql.lower()):#if sql.lower().find('where ') != -1 or sql.lower().find('where\n') != -1:

sql = '%s and %s in %%s' %(sql, in_attr)else:

sql= '%s where %s in %%s' %(sql, in_attr)while start

in_expr= "('%s')" % "','".join(in_list[start:end])else:

in_expr= "('%s')" % "','".join(in_list[start:])

result.extend(self.query(sql%in_expr))

start=end

end+=max_cntreturnresult#def get_autoincrement_id(self, tbl):

#'''Get the next auto increment id of table.

# #Return None if the table doesn't have an auto-increment id.

#'''

#self.execute('SHOW TABLE STATUS LIKE %s', (tbl,))

#result = self.get_rows(is_dict = True)

#if result[0]:

#return result[0]['Auto_increment']

#else:

#return None

defcommit(self):'''Commits the current transaction.'''

if self._db is notNone:

self._db.commit()defrollback(self):'''Rollback the last transaction.'''

if self._db is notNone:

self._db.rollback()#vim: set expandtab smarttab shiftwidth=4 tabstop=4: