Commonly used operation of MySQL under cmd and python
- 2021-08-17 01:17:11
- OfStack
Environment Configuration 1: Install mysql and 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 you will report c + + compile 9.0, import _ mysql and other errors
The windows10 64-bit operating system can be downloaded and installed from http://www.lfd.uci.edu/~ gohlke/pythonlibs/MySQL-Python package. For the installation method of whl and tar.gz under windows and Linux, please see my last article
1. Actions under the cmd command:
Connect mysql: mysql-u root-p
View all databases: show databases;
Create test database: create database test;
Delete database: drop database test;
Use (switch to) the test database: use test;
View the table under the current database: show tables;
Create an UserInfo table: create table UserInfo (id int (5) NOT NULL auto_increment, username varchar (10), password varchar (20) NOT NULL, PRIMARY KEY (id);
Delete table: drop table UserInfo;
Judging whether the data exists: select * from UserInfo where name like 'elijahxb';
Added 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';
Deleted data: delete from UserInfo; delete from UserInfo where id=1;
Disconnect: quit
2. Actions under python:
# -*- coding: utf-8 -*-
#!/usr/bin/env python
# @Time : 2017/6/4 18:11
# @Author : Elijah
# @Site :
# @File : sql_helper.py
# @Software: PyCharm Community Edition
import MySQLdb
class 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.execute(sqlcmd)
def SetDatabase(self,database):
return self.cursor.execute("use %s;"%database)
def GetDatabasesCount(self):
return self.cursor.execute("show databases;")
def GetTablesCount(self):
return self.cursor.execute("show tables;")
def GetFetchone(self, table = None):
if not table:
table = self.tablename
self.cursor.execute("select * from %s;"%table)
return self.cursor.fetchone()
def GetFetchmany(self,table=None,size=0):
if not table:
table = self.tablename
count = self.cursor.execute("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.execute("select * from %s;"%table)
return self.cursor.fetchall()
def SetInsertdata(self,table=None,keyinfo=None,value=None):
"""
:param table:
:param keyinfo: You can not pass this parameter, but at this time value Every 1 The number of fields in a piece of data must be the same as the number of fields in the database 1 To.
When this parameter is passed, it means that only the field value of the specified field is passed.
:param value: Type must be only 1 A tuple of group information, or a list of tuples containing multiple pieces of 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%s values(%s);" % (table,keyinfo,valuecenter)
print(sqlcmd)
print(value)
if execmany:
return self.cursor.executemany(sqlcmd,value)
else:
return self.cursor.execute(sqlcmd, value)