网站建设设计师助理岗位介绍/长春百度快速优化

  1 # -*- coding: utf-8 -*-
  2 #python operate mysql database
  3 import MySQLdb
  4 
  5 #数据库名称
  6 DATABASE_NAME = ''
  7 #host = 'localhost' or '172.0.0.1'
  8 HOST = ''
  9 #端口号
 10 PORT = ''
 11 #用户名称
 12 USER_NAME = ''
 13 #数据库密码
 14 PASSWORD = ''
 15 #数据库编码
 16 CHAR_SET = ''
 17 
 18 #初始化参数
 19 def init():
 20     global DATABASE_NAME
 21     DATABASE_NAME = 'test'
 22     global HOST
 23     HOST = 'localhost'
 24     global PORT
 25     PORT = '3306'
 26     global USER_NAME
 27     USER_NAME = 'root'
 28     global PASSWORD
 29     PASSWORD = 'root'
 30     global CHAR_SET
 31     CHAR_SET = 'utf8'
 32     
 33 #获取数据库连接
 34 def get_conn():
 35     init()
 36     return MySQLdb.connect(host = HOST, user = USER_NAME, passwd = PASSWORD, db = DATABASE_NAME, charset = CHAR_SET)
 37 
 38 #获取cursor
 39 def get_cursor(conn):
 40     return conn.cursor()
 41 
 42 #关闭连接
 43 def conn_close(conn):
 44     if conn != None:
 45         conn.close()
 46 
 47 #关闭cursor
 48 def cursor_close(cursor):
 49     if cursor != None:
 50         cursor.close()
 51 
 52 #关闭所有
 53 def close(cursor, conn):
 54     cursor_close(cursor)
 55     conn_close(conn)
 56 
 57 #创建表
 58 def create_table():
 59     sql = '''
 60     CREATE TABLE `student` (
 61     `id` int(11) NOT NULL,
 62     `name` varchar(20) NOT NULL,
 63     `age` int(11) DEFAULT NULL,
 64     PRIMARY KEY (`id`),
 65     UNIQUE KEY `name` (`name`)
 66     ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 67     '''
 68     conn = get_conn()
 69     cursor = get_cursor(conn)
 70     result = cursor.execute(sql)
 71     conn.commit()
 72     close(cursor, conn)
 73     return result
 74 
 75 #查询表信息
 76 def query_table(table_name):
 77     if table_name != '':
 78         sql = 'select * from ' + table_name
 79         conn = get_conn()
 80         cursor = get_cursor(conn)
 81         result = cursor.execute(sql)
 82         for row in cursor.fetchall():
 83             print(row)
 84             #for r in row:      #循环每一条数据
 85                 #print(r)
 86         close(cursor, conn)
 87     else:
 88         print('table name is empty!')
 89 
 90 #插入数据
 91 def insert_table():
 92     sql = 'insert into student(id, name, age) values(%s, %s, %s)'
 93     params = ('1', 'Hongten_a', '21')
 94     conn = get_conn()
 95     cursor = get_cursor(conn)
 96     result = cursor.execute(sql, params)
 97     conn.commit()
 98     close(cursor, conn)
 99     return result
100 
101 #更新数据
102 def update_table():
103     sql = 'update student set name = %s where id = 1'
104     params = ('HONGTEN')
105     conn = get_conn()
106     cursor = get_cursor(conn)
107     result = cursor.execute(sql, params)
108     conn.commit()
109     close(cursor, conn)
110     return result
111 
112 #删除数据
113 def delete_data():
114     sql = 'delete from student where id = %s'
115     params = ('1')
116     conn = get_conn()
117     cursor = get_cursor(conn)
118     result = cursor.execute(sql, params)
119     conn.commit()
120     close(cursor, conn)
121     return result
122 
123 #数据库连接信息   
124 def print_info():
125     print('数据库连接信息:' + DATABASE_NAME + HOST + PORT + USER_NAME + PASSWORD + CHAR_SET)
126 
127 #打印出数据库中表情况
128 def show_databases():
129     sql = 'show databases'
130     conn = get_conn()
131     cursor = get_cursor(conn)
132     result = cursor.execute(sql)
133     for row in cursor.fetchall():
134         print(row)
135         
136 #数据库中表情况
137 def show_tables():
138     sql = 'show tables'
139     conn = get_conn()
140     cursor = get_cursor(conn)
141     result = cursor.execute(sql)
142     for row in cursor.fetchall():
143         print(row)
144 
145    
146 def main():
147     show_tables()
148     #创建表
149     result = create_table()
150     print(result)
151     #查询表
152     query_table('student')
153     #插入数据
154     print(insert_table())
155     print('插入数据后....')
156     query_table('student')
157     #更新数据
158     print(update_table())
159     print('更新数据后....')
160     query_table('student')
161     #删除数据
162     delete_data()
163     print('删除数据后....')
164     query_table('student')
165     print_info()
166     #数据库中表情况
167     show_tables()
168     
169 
170 if __name__ == '__main__':
171     main()