Common MySQL operations in cmd and python

Common MySQL operations in cmd and python

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: Common MySQL operations in cmd and python

#-*-Coding: UTF-8 -*-

#! /Usr/bin/env python

# @ Time:

# @ 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.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 GetTablesCount (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 the same as 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 list of tuples with only one set of information or tuples containing multiple information.

: Return:

"""

If not table:

Table = self. tablename

Slist = []

If type (value) = tuple:

Valuelen = value

Execmany = False

Else:

Valuelen = value [0]

Execute = 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 execmany:

Return self.cursor.exe cute.pdf (sqlcmd, value)

Else:

Return self.cursor.exe cute (sqlcmd, value)