Common MySQL operations in cmd and python

Source: Internet
Author: User
Tags pycharm community edition

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.