Connecting to MySQL using Python

1. Install mysql server & mysql client
   sudo apt-get install mysql-server
   sudo apt-get install mysql-client
2.  Install MySQL-python
   To enable python to operate mysql requires MySQL-python package, it is python operation mysql essential modules.Download: https://pypi.python.org/pypi/MySQL-python/

   Download the MySQL-python-1.2.5.zip file and extract it directly. Go to MySQL-python-1.2.5 directory:

   python setup.py install
   check whether the MySQLdb module can be imported normally.

   root@ddos-ThinkPad-W540:/home/ddos/Downloads/MySQL-python-1.2.5# python
   
   Python 2.7.13 (default, Jan 19 2017, 14:48:08) 
   
   [GCC 6.3.0 20170118] on linux2
   
   Type "help", "copyright", "credits" or "license" for more information.
   
   >>> import MySQLdb
3. If you are not similar mysql, please read this post.
4. Interaction with mysql using python
   >>> conn = MySQLdb.connect(host=’localhost’,port = 3306,user=’root’, passwd=’ddos′,db =’classmate’,) # connect() method used to create a database connection, which can specify the parameters: user name, password, host and other information. This is just a connection to the database, in order to operate the database need to create a cursor.
   cur = conn.cursor() #the cursor() method to create a cursor.
   >>> cur.execute(“create table student(id int ,name varchar(20),class varchar(30),age varchar(10))”) #Through the cursor cur operation execute () method can write pure sql statement. Through the execute () method to write such as sql statement to operate on the data.
   >>>cur.close() #Close the cursor
   >>>conn.commit() #Conn.commit () method in the submission of things, in the database to insert a data must have this method, otherwise the data will not be really inserted.
   >>>conn.close() #closes the database connection

   import MySQLdb
   
   
   
   conn= MySQLdb.connect(
   
           host='localhost',
   
           port = 3306,
   
           user='root',
   
           passwd='ddos',
   
           db ='classmate',
   
           )
   
   cur = conn.cursor()
   
   #cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")
   
   #cur.execute("insert into student values('2','DDOS','4 year 4 class','11')")
   
   #cur.execute("update student set class='3 year 1 class' where name = 'DDOS'")
   
   #cur.execute("delete from student where age='11'")
   
   
   
   cur.close()
   
   conn.commit()
   
   conn.close()

    

5. Insert data to table 

   >>>cur.execute(“insert into student values(‘2′,’DDOS’,’4 year 4 class’,’11′)”)

   To insert the new data, you must change the value of this statement. You can make the following modifications:

   import MySQLdb
   
    
   
   conn= MySQLdb.connect(
   
           host='localhost',
   
           port = 3306,
   
           user='root',
   
           passwd='ddos',
   
           db ='classmate',
   
           )
   
   cur = conn.cursor()
   
    
   
   sqli="insert into student values(%s,%s,%s,%s)"
   
   cur.execute(sqli,('3','blackhat','2 year 2 class','9'))
   
    
   
   cur.close()
   
   conn.commit()
   
   conn.close()

   The executemany() method can insert multiple values ​​at once, execute the singular sql statement, but repeat the parameters in the parameter list. The return value is the number of rows affected.

   import MySQLdb
   
    
   
   conn= MySQLdb.connect(
   
           host='localhost',
   
           port = 3306,
   
           user='root',
   
           passwd='ddos',
   
           db ='classmate',
   
           )
   
   cur = conn.cursor()
   
   sqli="insert into student values(%s,%s,%s,%s)"
   
   cur.executemany(sqli,[
   
       ('5','Alex','1 year 1 class','8'),
   
       ('3','Philip','4 year 1 class','8'),
   
       ('3','Crusior','3 year 2 class','4'),
   
       ])
   
    
   
   cur.close()
   
   conn.commit()
   
   conn.close()

    

6. Query data
   >>>aa=cur.execute(“select * from student”)print aa
   The fetchone() method can help us get the data in the table, but every time we execute cur.fetchone(), we do not have the same data. In other words, I did not execute the cursor once from the first data in the table The location of a data, so I re-executed when the second is the data.
   acroll(0, ‘absolute’) method You can navigate the cursor to the first data in the table

   >>> import MySQLdb
   
   >>> conn = MySQLdb.connect(host='localhost',port = 3306,user='root',    passwd='ddos',db ='classmate',)
   
   >>> cur = conn.cursor()
   
   >>> cur.execute("select * from student")
   
   5L
   
   >>> cur.fetchone()
   
   (1L, 'Alex','1 year 1 class','8')
   
   >>> cur.fetchone()
   
   (3L, 'Philip','4 year 1 class','8')
   
   >>> cur.fetchone()
   
   (3L, 'Crusior','3 year 2 class','4')
   
   ...
   
   >>>cur.scroll(0,'absolute')

   import MySQLdb
   
   conn= MySQLdb.connect(
   
           host='localhost',
   
           port = 3306,
   
           user='root',
   
           passwd='123456',
   
           db ='test',
   
           )
   
   cur = conn.cursor()
   
   aa=cur.execute("select * from student")
   
   print aa
   
   info = cur.fetchmany(aa)
   
   for ii in info:
   
       print ii
   
   cur.close()
   
   conn.commit()
   
   conn.close()