Common MySQL operations in cmd and python, mysql#python

Common MySQL operations in cmd and python, mysql#python

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:

　　

1 #-*-coding: UTF-8-*-2 #! /Usr/bin/env python 3 4 # @ Time: 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. co Nn = 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.exe cute (sqlcmd) 26 def SetDatabase (self, database): 27 return self.cursor.exe cute ("use % s; "% database) 28 def GetDatabasesCount (self): 29 Return self.cursor.exe cute ("show databases;") 30 def GetTablesCount (self): 31 return self.cursor.exe cute ("show tables;") 32 def GetFetchone (self, table = None ): 33 if not table: 34 table = self. tablename35 self.cursor.exe cute ("select * from % s;" % table) 36 return self. cursor. fetchone () 37 def getfetchone (self, table = None, size = 0): 38 if not table: 39 table = self. tablename40 count = self.cursor.exe cute ("sele Ct * from % s; "% table) 41 return self. cursor. fetchmany (size) 42 def GetFetchall (self, table = None): 43 ''' 44: param table: List 45: return: 46 ''' 47 if not table: 48 table = self. tablename49 self.cursor.exe cute ("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: you can skip this parameter, but the number of fields in each data entry of value must be the same as that in the database. When this parameter is set to 55, only the field value of the specified field is used. 56: param value: the type must be a tuples with only one set of information, or a list composed of tuples containing multiple information 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 values (% s);" % (table, keyinfo, valuecenter) 75 print (sqlcmd) 76 print (value) 77 if execute values: 78 return self.cursor.exe cute.pdf (sqlcmd, value) 79 else: 80 return self.cursor.exe cute (sqlcmd, value)

MySqlHelper