Using python to operate mysql databases

Python-related mysql database operation instances

#-*-Coding: UTF-8-*-# python operate mysql databaseimport MySQLdb # Database name DATABASE_NAME = ''# host = 'localhost' or '2017. 0.0.1 'host = ''# PORT number PORT ='' # User name USER_NAME = ''# database PASSWORD ='' # Database code CHAR_SET = ''# initialization parameter def init (): global DATABASE_NAME = 'test' global HOST = 'localhost' global PORT = '000000' global USER_NAME = 'root' global PASSWORD = 'root' global CHAR_SET = 'utf8' # obtain the database connection def get_conn (): init () return MySQLdb. connect (host = HOST, user = USER_NAME, passwd = PASSWORD, db = DATABASE_NAME, charset = CHAR_SET) # get cursordef get_cursor (conn): return conn. cursor () # Close connection def conn_close (conn): if conn! = None: conn. close () # close cursordef cursor_close (cursor): if cursor! = None: cursor. close () # close All def close (cursor, conn): cursor_close (cursor) conn_close (conn) # create table def create_table (): SQL = '''create table'student '('id' int (11) NOT NULL, 'name' varchar (20) NOT NULL, 'age' int (11) default null, primary key ('id'), unique key 'name' ('name') ENGINE = InnoDB default charset = utf8''' conn = get_conn () cursor = get_cursor (conn) result = cursor.exe cute (SQL) conn. commi T () close (cursor, conn) return result # query the table information def query_table (table_name): if table_name! = '': SQL = 'select * from' + table_name conn = get_conn () cursor = get_cursor (conn) result = cursor.exe cute (SQL) for row in cursor. fetchall (): print (row) # for r in row: # loop each piece of data # print (r) close (cursor, conn) else: print ('Table name is empty! ') # Insert data def insert_table (): SQL = 'Insert into student (id, name, age) values (% s, % s, % s) 'params = ('1', 'hongten _ A', '21') conn = get_conn () cursor = get_cursor (conn) result = cursor.exe cute (SQL, params) conn. commit () close (cursor, conn) return result # update data def update_table (): SQL = 'update student set name = % s where id = 1 'params = ('hongten') conn = get_conn () cursor = get_cursor (conn) result = cursor.exe cute (SQL, params) conn. commit () close (cursor, conn) return result # delete data def delete_data (): SQL = 'delete from student where id = % s' params = ('1 ') conn = get_conn () cursor = get_cursor (conn) result = cursor.exe cute (SQL, params) conn. commit () close (cursor, conn) return result # Database Connection information def print_info (): print ('database connection information: '+ DATABASE_NAME + HOST + PORT + USER_NAME + PASSWORD + CHAR_SET) # print the database table status def show_databases (): SQL = 'show databases' conn = get_conn () cursor = get_cursor (conn) result = cursor.exe cute (SQL) for row in cursor. fetchall (): print (row) # def show_tables (): SQL = 'show tables 'conn = get_conn () cursor = get_cursor (conn) result = cursor.exe cute (SQL) for row in cursor. fetchall (): print (row) def main (): show_tables () # create table result = create_table () print (result) # Query table query_table ('student ') # insert data print (insert_table () print ('After inserting data .... ') query_table ('student') # update data print (update_table () print ('After data is updated .... ') query_table ('student') # delete the data delete_data () print ('After the data is deleted .... ') query_table ('student') print_info () # show_tables () if _ name _ = '_ main _': main ()