Common MySQL operations in cmd and python, mysqlpython

Common MySQL operations in cmd and python, mysqlpython

Environment configuration 1: Install mysql and add the bin directory of mysql to the Environment Variables

Environment configuration 2: Install MySQL-python in Python

Download and install the SDK based on your operating system. Otherwise, errors such as c ++ compile 9.0 and import _ mysql will be reported.

Windows 10 64-bit operating system can go to http://www.lfd.uci.edu /~ Gohlke/pythonlibs/download and install the mysql-pythonpackage. To learn how to install whland tar.gz in windows and Linux, see my previous article.

I. operations under the cmd command:

Connect to mysql: mysql-u root-p

View All databases: show databases;

Create a test database: create database test;

Delete database: drop database test;

Use (switch to) test Database: use test;

View the tables in the current database: show tables;

Create UserInfo table: create table UserInfo (id int (5) not null auto_increment, username varchar (10), password varchar (20) not null, primary key (id ));

Drop table UserInfo;

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

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

Query 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

Ii. Operations in python:

#-*-Coding: UTF-8 -*-#! /Usr/bin/env python # @ Time: #/6/4 18:11 # @ Author: Elijah # @ Site: # @ File: SQL _helper.py # @ Software: PyCharm Community Editionimport MySQLdbclass MySqlHelper (object ): def _ init _ (self, ** args): self. ip = args. get ("IP") self. user = args. get ("User") self. password = args. get ("Password") self. tablename = args. get ("Table") self. port = 3306 self. conn = self. conn = MySQLdb. connect (host = self. ip, user = Self. user, passwd = self. password, port = self. port, connect_timeout = 5, autocommit = True) self. cursor = self. conn. cursor () def Close (self): self. cursor. close () self. conn. close () def execute (self, sqlcmd): return self.cursor.exe cute (sqlcmd) def SetDatabase (self, database): return self.cursor.exe cute ("use % s;" % database) def GetDatabasesCount (self): return self.cursor.exe cute ("show databases;") def GetTablesCou Nt (self): return self.cursor.exe cute ("show tables;") def GetFetchone (self, table = None): if not table: table = self. tablename self.cursor.exe cute ("select * from % s;" % table) return self. cursor. fetchone () def GetFetchmany (self, table = None, size = 0): if not table: table = self. tablename count = self.cursor.exe cute ("select * from % s;" % table) return self. cursor. fetchmany (size) def GetFetchall (self, table = None): ''': param table: List: return: ''' if not table: table = self. tablename self.cursor.exe cute ("select * from % s;" % table) return self. cursor. fetchall () def SetInsertdata (self, table = None, keyinfo = None, value = None): "": param table: param keyinfo: You can skip this parameter, however, the number of fields in each data entry of value must be consistent with the number of fields in the database. When this parameter is set, only the field value of the specified field is used.: Param value: the type must be a set of information tuples, or a list of tuples containing multiple information: return: "if not table: table = self. tablename slist = [] if type (value) = tuple: valuelen = value execmany = False else: valuelen = value [0] execmany = True for each in range (len (valuelen): slist. append ("% s") valuecenter = ",". join (slist) if not keyinfo: sqlcmd = "insert into % s values (% s);" % (table, valuecenter) else: sqlcmd = "insert into % s values (% s);" % (table, keyinfo, valuecenter) print (sqlcmd) print (value) if execute values: return self.cursor.exe cute.pdf (sqlcmd, value) else: return self.cursor.exe cute (sqlcmd, value)

The common operations of MySQL in cmd and python are all shared by the editor. I hope to give you a reference, and I hope you can provide more support for the customer's house.