python databaseoperate_python操作mysql数据库的相关操作实例

python操作mysql数据库的相关操作实例#-*- coding: utf-8 -*-#python operate mysql database

importMySQLdb#数据库名称

DATABASE_NAME = ''

#host = 'localhost' or '172.0.0.1'

HOST = ''

#端口号

PORT = ''

#用户名称

USER_NAME = ''

#数据库密码

PASSWORD = ''

#数据库编码

CHAR_SET = ''

#初始化参数

definit():globalDATABASE_NAME

DATABASE_NAME= 'test'

globalHOST

HOST= 'localhost'

globalPORT

PORT= '3306'

globalUSER_NAME

USER_NAME= 'root'

globalPASSWORD

PASSWORD= 'root'

globalCHAR_SET

CHAR_SET= 'utf8'

#获取数据库连接

defget_conn():

init()return MySQLdb.connect(host = HOST, user = USER_NAME, passwd = PASSWORD, db = DATABASE_NAME, charset =CHAR_SET)#获取cursor

defget_cursor(conn):returnconn.cursor()#关闭连接

defconn_close(conn):if conn !=None:

conn.close()#关闭cursor

defcursor_close(cursor):if cursor !=None:

cursor.close()#关闭所有

defclose(cursor, conn):

cursor_close(cursor)

conn_close(conn)#创建表

defcreate_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.execute(sql)

conn.commit()

close(cursor, conn)returnresult#查询表信息

defquery_table(table_name):if table_name != '':

sql= 'select * from' +table_name

conn=get_conn()

cursor=get_cursor(conn)

result=cursor.execute(sql)for row incursor.fetchall():print(row)#for r in row: #循环每一条数据

#print(r)

close(cursor, conn)else:print('table name is empty!')#插入数据

definsert_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.execute(sql, params)

conn.commit()

close(cursor, conn)returnresult#更新数据

defupdate_table():

sql= 'update student set name = %s where id = 1'params= ('HONGTEN')

conn=get_conn()

cursor=get_cursor(conn)

result=cursor.execute(sql, params)

conn.commit()

close(cursor, conn)returnresult#删除数据

defdelete_data():

sql= 'delete from student where id = %s'params= ('1')

conn=get_conn()

cursor=get_cursor(conn)

result=cursor.execute(sql, params)

conn.commit()

close(cursor, conn)returnresult#数据库连接信息

defprint_info():print('数据库连接信息:' + DATABASE_NAME + HOST + PORT + USER_NAME + PASSWORD +CHAR_SET)#打印出数据库中表情况

defshow_databases():

sql= 'show databases'conn=get_conn()

cursor=get_cursor(conn)

result=cursor.execute(sql)for row incursor.fetchall():print(row)#数据库中表情况

defshow_tables():

sql= 'show tables'conn=get_conn()

cursor=get_cursor(conn)

result=cursor.execute(sql)for row incursor.fetchall():print(row)defmain():

show_tables()#创建表

result =create_table()print(result)#查询表

query_table('student')#插入数据

print(insert_table())print('插入数据后....')

query_table('student')#更新数据

print(update_table())print('更新数据后....')

query_table('student')#删除数据

delete_data()print('删除数据后....')

query_table('student')

print_info()#数据库中表情况

show_tables()if __name__ == '__main__':

main()