Summary and sharing of common operations of MySQL under cmd and python

Environment configuration 1: Install mysql, add the bin directory of mysql to the environment variable

Environment configuration 2: python install MySQL-Python

Please download and install according to your own operating system, otherwise it will report c ++ compile 9.0, import _mysql and other errors

Windows10 64-bit operating system can go to http://www.lfd.uci.edu/~gohlke/pythonlibs/ to download and install the MySQL-Python package. As for whl and tar For the installation method of .gz under Windows and Linux, please see my previous article

1. Operation under cmd command:

Connect to mysql: mysql -u root -p

View all databases: show databases;

Create test database: create database test;

Delete database: drop database test;

Use (switch to) test database ：use test;

View the tables under the current database: show tables;

Create the UserInfo table: create table UserInfo(id int(5) NOT NULL auto_increment,username varchar(10),password varchar(20) NOT NULL,PRIMARY KEY(id));

Delete the table: drop table UserInfo;

Determine whether the data exists: select * from UserInfo where name like 'elijahxb';

Add data: insert into UserInfo(username,password) value('eljiahxb','123456');

Check data: select * from UserInfo; select id from UserInfo; select username from UserInfo;

Change data: update UserInfo set username = 'Zus' where id=1; update UserInfo set username='Zus';

Delete data: delete from UserInfo ; Delete from UserInfo where id=1;

Disconnect: quit

2. Operation under python:

　

 1 # -*- coding: utf-8 -*- 2 #!/usr/bin/env python 3  4 # @Time    : 2017/6/4 18:11 5 # @Author  : Elijah 6 # @Site    :  7 # @File    : sql_helper.py 8 # @Software: PyCharm Community Edition 9 import MySQLdb10 11 class MySqlHelper(object):12     def __init__(self,**args):13         self.ip = args.get("IP")14         self.user = args.get("User")15         self.password = args.get("Password")16         self.tablename = args.get("Table")17         self.port = 330618         self.conn = self.conn = MySQLdb.Connect(host=self.ip,user=self.user,passwd=self.password,port=self.port,connect_timeout=5,autocommit=True)19         self.cursor = self.conn.cursor()20 21     def Close(self):22         self.cursor.close()23         self.conn.close()24     def execute(self,sqlcmd):25         return self.cursor.execute(sqlcmd)26     def SetDatabase(self,database):27         return self.cursor.execute("use %s;"%database)28     def GetDatabasesCount(self):29         return self.cursor.execute("show databases;")30     def GetTablesCount(self):31         return self.cursor.execute("show tables;")32     def GetFetchone(self, table = None):33         if not table:34             table = self.tablename35         self.cursor.execute("select * from %s;"%table)36         return self.cursor.fetchone()37     def GetFetchmany(self,table=None,size=0):38         if not table:39             table = self.tablename40         count = self.cursor.execute("select * from %s;"%table)41         return self.cursor.fetchmany(size)42     def GetFetchall(self,table=None):43         '''44         :param table: 列表45         :return:46         '''47         if not table:48             table = self.tablename49         self.cursor.execute("select * from %s;"%table)50         return self.cursor.fetchall()51     def SetInsertdata(self,table=None,keyinfo=None,value=None):52         """53         :param table:54         :param keyinfo:可以不传此参数，但此时value每一条数据的字段数必须与数据库中的字段数一致。55                         传此参数时，则表示只穿指定字段的字段值。56         :param value:类型必须为只有一组信息的元组，或者包含多条信息的元组组成的列表57         :return:58         """59         if not table:60             table = self.tablename61         slist = []62         if type(value)==tuple:63             valuelen = value64             execmany = False65         else:66             valuelen = value[0]67             execmany = True68         for each in range(len(valuelen)):69             slist.append("%s")70         valuecenter = ",".join(slist)71         if not keyinfo:72             sqlcmd = "insert into %s values(%s);"%(table,valuecenter)73         else:74             sqlcmd = "insert into %s%s values(%s);" % (table,keyinfo,valuecenter)75         print(sqlcmd)76         print(value)77         if execmany:78             return self.cursor.executemany(sqlcmd,value)79         else:80             return self.cursor.execute(sqlcmd, value)

MySqlHelper

The above is the detailed content of Summary and sharing of common operations of MySQL under cmd and python. For more information, please follow other related articles on the PHP Chinese website!