MySQL yesWeb The most widely used database server in the world,SQLite Is lightweight, Embeddable, But can't withstand high concurrent access, Suitable for desktop and mobile applications. andMySQL Is a database designed for the server side, Can withstand high concurrent access, At the same time, the memory occupied is far greater thanSQLite. in addition,MySQL There are many internal database engines, The most commonly used engine supports database transactionsInnoDB.

One, installMySQL

I won't go into details here, If you can't install it, please refer to the following blog

 http://www.cnblogs.com/wj-1314/p/7573242.html
<https://www.cnblogs.com/wj-1314/p/7573242.html>

Two, installMySQL-python

Want to makepython Can operatemysql  NeedMySQL-python drive, It ispython  operationmysql Essential modules.

Download address:https://pypi.python.org/pypi/MySQL-python/
<https://pypi.python.org/pypi/MySQL-python/>

downloadMySQL-python-1.2.5.zip  Decompress the file directly. Get intoMySQL-python-1.2.5 Catalog:

>>python setup.py install

Then installpymysql
pip install pymysql
  

Three, testpymysql Modular


   The test is very simple, inspectpymysql Whether the module can be imported normally.( When operating the database,python2 General usemysqldb, But inpython3 No longer supported inmysqldb 了, We can usepymysql andmysql.connector. All operations in this article arepython3 Ofpymysql Completed below.)



No error promptMySQLdb Module not found, Description installationOK 

Four,mysql  Basic operation of
mysql> show databases; // View all current databases +--------------------+ | Database |
+--------------------+ | information_schema | | csvt | | csvt04 | | mysql | |
performance_schema | | test | +--------------------+ rows in set (0.18 sec)
mysql> use test; // Function andtest data base Database changed mysql> show tables;
// Seetest Table below Library Empty set (0.00 sec) // Establishuser surface,name andpassword Two fields mysql> CREATE
TABLE user (name VARCHAR(20),password VARCHAR(20)); Query OK, 0 rows affected
(0.27 sec) // towardsuser Insert several pieces of data into the table mysql> insert into user values('Tom','1321'); Query
OK, 1 row affected (0.05 sec) mysql> insert into user values('Alen','7875');
Query OK, 1 row affected (0.08 sec) mysql> insert into user
values('Jack','7455'); Query OK, 1 row affected (0.04 sec) // Seeuser Table data mysql>
select * from user; +------+----------+ | name | password | +------+----------+
| Tom | 1321 | | Alen | 7875 | | Jack | 7455 | +------+----------+ rows in set
(0.01 sec) // deletename Be equal toJack Data mysql> delete from user where name = 'Jack';
Query OK, 1 rows affected (0.06 sec) // modifyname Be equal toAlen Ofpassword by 1111 mysql>
update user set password='1111' where name = 'Alen'; Query OK, 1 row affected
(0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 // View table contents mysql> select * from
user; +--------+----------+ | name | password | +--------+----------+ | Tom |
1321 | | Alen | 1111 | +--------+----------+ rows in set (0.00 sec)
  

Five,python  operationmysql Database foundation

   Steal a picture here, Show in flow chartpython operationMySQL Process of database:



 

1, implementSQL, The specific statement is as follows:
#coding=utf-8 import MySQLdb
# Open database connection conn= MySQLdb.connect( host='localhost', port = 3306, user='root',
passwd='123456', db ='test', )

# Usecursor() Method get operation cursor cur = conn.cursor() # Create data table #cur.execute("create table
student(id int ,name varchar(20),class varchar(30),age varchar(10))") # Insert a piece of data
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")
# Modify data of query criteria #cur.execute("update student set class='3 year 1 class' where name =
'Tom'") # Delete data of query criteria #cur.execute("delete from student where age='9'")
# Close cursor cur.close()

# Submission, Otherwise, the new or modified data cannot be saved conn.commit()

# Close database connection conn.close()
2, Get auto increment of newly created dataID  
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', passwd='123', db='t1') cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)",
[("1.1.1.11",1),("1.1.1.11",2)]) conn.commit() cursor.close() conn.close() #
Get the latest auto incrementID new_id = cursor.lastrowid
3, Get query data
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', passwd='123', db='t1') cursor = conn.cursor()
cursor.execute("select * from hosts") # Get the first row of data row_1 = cursor.fetchone() #
Before gettingn Row data # row_2 = cursor.fetchmany(3) # Get all data # row_3 = cursor.fetchall()
conn.commit() cursor.close() conn.close()
          fetchone() Method can help us get the data in the table, But every time
cur.fetchone()  The data is not the same, In other words, I didn't do it once, The cursor moves from the first data in the table to the next data, therefore, When I execute again, I get the second data.
fetchone() The return value of the function is a single tuple, It's a line of records, Without, Just returnnull

  fetchall() The return value of the function is multiple tuples, That is, multiple line records are returned, Without, The return is(),

notes: stayfetch Data in sequence, have access tocursor.scroll(num,mode) To move the cursor position, as:

* cursor.scroll(1,mode='relative')  # Move relative to current position
* cursor.scroll(2,mode='absolute') # Relative absolute position movement
4,fetch data type

   About the data obtained by default is the primitive type, If you want or dictionary type data, Namely:
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', passwd='123', db='t1') # Cursor set to dictionary type cursor =
conn.cursor(cursor=pymysql.cursors.DictCursor) r = cursor.execute("call p1()")
result = cursor.fetchone() conn.commit() cursor.close() conn.close()
5, insert data

Pass aboveexecute() Write pure in methodsql Statement is not convenient to insert data. as:

>>>cur.execute("insert into student values('2','Tom','3 year 2 class','9')")

I want to insert new data, The value in this statement must be modified. We can make the following changes:
#coding=utf-8 import MySQLdb conn= MySQLdb.connect( host='localhost', port =
3306, user='root', passwd='123456', db ='test', ) cur = conn.cursor() # Insert a piece of data
sqli="insert into student values(%s,%s,%s,%s)" cur.execute(sqli,('3','Huhu','2
year 1 class','7')) cur.close() conn.commit() conn.close()
     What if you want to insert multiple values into a data table at once?

    executemany() Method can insert multiple values at a time, Single executionsql Sentence, But repeat the parameters in the parameter list, The return value is the number of rows affected.
#coding=utf-8 import MySQLdb conn= MySQLdb.connect( host='localhost', port =
3306, user='root', passwd='123456', db ='test', ) cur = conn.cursor() # Insert multiple records at a time
sqli="insert into student values(%s,%s,%s,%s)" cur.executemany(sqli,[
('3','Tom','1 year 1 class','6'), ('3','Jack','2 year 1 class','7'),
('3','Yaheng','2 year 2 class','7'), ]) cur.close() conn.commit() conn.close()
   

5.6 Practicepython operationMySQL
#!/usr/bin/env python # coding=utf-8 import pymysql def connectdb():
print(' connection tomysql The server...') # Open database connection # User name:hp,
Password:Hp12345. User name and password need to be changed to your ownmysql User name and password, And to create a databaseTESTDB, And inTESTDB Create a good table in the databaseStudent db
= pymysql.connect("localhost","hp","Hp12345.","TESTDB") print(' Connect up!') return
db def createtable(db): # Usecursor() Method get operation cursor cursor = db.cursor() #
If a table existsSutdent Delete first cursor.execute("DROP TABLE IF EXISTS Student") sql = """CREATE
TABLE Student ( ID CHAR(10) NOT NULL, Name CHAR(8), Grade INT )""" # EstablishSutdent surface
cursor.execute(sql) def insertdb(db): # Usecursor() Method get operation cursor cursor = db.cursor()
# SQL Insert statement sql = """INSERT INTO Student VALUES ('001', 'CZQ', 70), ('002',
'LHQ', 80), ('003', 'MQ', 90), ('004', 'WH', 80), ('005', 'HP', 70), ('006',
'YF', 66), ('007', 'TEST', 100)""" #sql = "INSERT INTO Student(ID, Name, Grade)
\ # VALUES ('%s', '%s', '%d')" % \ # ('001', 'HP', 60) try: # implementsql Sentence
cursor.execute(sql) # Commit to database execution db.commit() except: # Rollback in case there is
any error print ' Insert data failed!' db.rollback() def querydb(db): # Usecursor() Method get operation cursor
cursor = db.cursor() # SQL Query statement #sql = "SELECT * FROM Student \ # WHERE Grade >
'%d'" % (80) sql = "SELECT * FROM Student" try: # implementSQL Sentence cursor.execute(sql) #
Get all records list results = cursor.fetchall() for row in results: ID = row[0] Name =
row[1] Grade = row[2] # Print results print "ID: %s, Name: %s, Grade: %d" % \ (ID, Name,
Grade) except: print "Error: unable to fecth data" def deletedb(db): #
Usecursor() Method get operation cursor cursor = db.cursor() # SQL Delete statement sql = "DELETE FROM Student
WHERE Grade = '%d'" % (100) try: # implementSQL Sentence cursor.execute(sql) # Submit revision
db.commit() except: print ' Failed to delete data!' # Rollback on error db.rollback() def updatedb(db): #
Usecursor() Method get operation cursor cursor = db.cursor() # SQL UPDATE statement sql = "UPDATE Student SET
Grade = Grade + 3 WHERE ID = '%s'" % ('003') try: # implementSQL Sentence cursor.execute(sql)
# Commit to database execution db.commit() except: print ' Failed to update data!' # Rollback on error db.rollback() def
closedb(db): db.close() def main(): db = connectdb() # ConnectMySQL data base
createtable(db) # Create table insertdb(db) # insert data print '\n After inserting data:' querydb(db)
deletedb(db) # Delete data print '\n After deleting data:' querydb(db) updatedb(db) # Update data print
'\n After updating data:' querydb(db) closedb(db) # close database if __name__ == '__main__': main()
  

Six, Consolidation exercises  

Exercises:
        Reference table structure:
            customer type

            User information

            Jurisdiction

            customer type& Jurisdiction
        function:

            # Land, register, Retrieve password
            # user management
            # customer type
            # Privilege management
            # Assign authority

        Special: Program only one executable

 

 python python operationSQLite

  SQLite Is an embedded database, Its database is a file. BecauseSQLite Itself isC Written, And it's very small. therefore, Often integrated into various applications, Even iniOS and
Android OfAPP Can be integrated in.

  Python It's built in.SQLite3, So inpython Use inSQLite, No need to install anything, Direct use.

   in useSQLite before, We need to figure out some concepts first:

   A table is a collection of relational data in a database, A database usually contains multiple tables, Like a student's watch, Class table, School watch, etc. Table and table are associated by foreign key.

   To operate on a relational database, First you need to connect to the database, A database connection becomesConnection;

   After connecting to the database, Cursor needs to be opened, Be calledCursor, adoptCursor implementSQL Sentence, Then get the execution results.

  Python It defines a set of operation databaseAPI Interface, Any database to connect toPython, Only compliance is requiredPython Standard database driver.

   BecauseSQLite The driver is built into thePython Standard library, So we can operate it directlySQLite data base.

   We arePython Interactive command line practice:
# ImportSQLite drive: >>> import sqlite3 # connection toSQLite data base # The database file istest.db #
If the file does not exist, Automatically created in the current directory: >>> conn = sqlite3.connect('test.db') # Create aCursor: >>>
cursor = conn.cursor() # Execute oneSQL Sentence, Establishuser surface: >>> cursor.execute('create table
user (id varchar(20) primary key, name varchar(20))') <sqlite3.Cursor object at
0x10f8aa260> # Continue with oneSQL Sentence, Insert a record: >>> cursor.execute('insert into user (id,
name) values (\'1\', \'Michael\')') <sqlite3.Cursor object at 0x10f8aa260> #
adoptrowcount Get the number of inserted rows: >>> cursor.rowcount 1 # CloseCursor: >>> cursor.close() # Submission of affairs:
>>> conn.commit() # CloseConnection: >>> conn.close()
   Let's try the records again:
>>> conn = sqlite3.connect('test.db') >>> cursor = conn.cursor() # Execute query statement: >>>
cursor.execute('select * from user where id=?', ('1',)) <sqlite3.Cursor object
at 0x10f8aa340> # Get query result set: >>> values = cursor.fetchall() >>> values [('1',
'Michael')] >>> cursor.close() >>> conn.close()
   UsePython OfDB-API Time, Just make sureconnection andcursor object, Remember to close it when you open it, You can use it at ease.

   Usecursor Object executioninsert,update,delete Statement time, Execution results arerowcount Returns the number of rows affected, You can get the execution results.

   Usecursor Object executionselect Statement time, adoptfeatchall() You can get the result set, The result set is alist, Each element is atuple, Corresponding to one line of record.

   IfSQL Statement with parameters, Then you need to pass the parameters to theexecute() Method, There are several? Placeholders must correspond to several parameters, for example:
ursor.execute('select * from user where name=? and pwd=?', ('abc', 'password'))
Practice:
import os, sqlite3 db_file = os.path.join(os.path.dirname(__file__),
'test.db') print(db_file) #
E:/backup/pycode/now/ProcessDataPreprocessing/code\test.db if
os.path.isfile(db_file): os.remove(db_file) # Initialization data conn =
sqlite3.connect(db_file) cursor = conn.cursor() sql1 = 'create table user(id
varchar(20) primary key , name varchar(20), score int)' cursor.execute(sql1)
sql2 = "insert into user values ('001','james', 99)" cursor.execute(sql2) sql3
= "insert into user values ('002','durant', 99)" cursor.execute(sql3)
cursor.close() conn.commit() conn.close()
   Query operation:
# Query record: conn = sqlite3.connect('test.db') cursor = conn.cursor() # Execute query statement:
sql4 = 'select * from user ' cursor.execute(sql4) # Get query result set: values =
cursor.fetchall() print(values) cursor.close() conn.close()
  

 

  Reference:https://www.2cto.com/database/201807/761697.html