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

One , install MySQL

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 , install MySQL-python

To make python Operational mysql  We need MySQL-python drive , It's python  operation mysql Essential modules .

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

download MySQL-python-1.2.5.zip  Decompress the file directly . get into MySQL-python-1.2.5 catalog :

>>python setup.py install

Then install pymysql
pip install pymysql
  

Three , test pymysql modular


   The test is very simple , inspect pymysql Whether the module can be imported normally .( When operating the database ,python2 General use mysqldb, But in python3 No longer supported in mysqldb 了 , We can use pymysql and mysql.connector. All operations in this article are python3 Of pymysql Next completed .)



No error prompt MySQLdb Module not found , Instruction installation OK 

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 and test data base Database changed mysql> show tables;
// see test Table below Library Empty set (0.00 sec) // establish user surface ,name and password Two fields mysql> CREATE
TABLE user (name VARCHAR(20),password VARCHAR(20)); Query OK, 0 rows affected
(0.27 sec) // towards user 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) // see user Table data mysql>
select * from user; +------+----------+ | name | password | +------+----------+
| Tom | 1321 | | Alen | 7875 | | Jack | 7455 | +------+----------+ rows in set
(0.01 sec) // delete name be equal to Jack Data for mysql> delete from user where name = 'Jack';
Query OK, 1 rows affected (0.06 sec) // modify name be equal to Alen Of password 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  operation mysql Database foundation

   A picture of stealing here , Show in flow chart python operation MySQL Process of database :



 

1, implement SQL, 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', )

# use cursor() 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()

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

# Close database connection conn.close()
2, Get auto increment of newly created data ID  
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 increment ID 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 acquisition n 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 go back null

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

notes : stay fetch Data in sequence , have access to cursor.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

Through the top execute() Write pure in method sql 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 , Executive singleton sql 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 practice python operation MySQL
#!/usr/bin/env python # coding=utf-8 import pymysql def connectdb():
print(' connection to mysql The server ...') # Open database connection # user name :hp,
password :Hp12345. User name and password need to be changed to your own mysql User name and password , And to create a database TESTDB, And TESTDB Create a good table in the database Student db
= pymysql.connect("localhost","hp","Hp12345.","TESTDB") print(' Connected !') return
db def createtable(db): # use cursor() Method get operation cursor cursor = db.cursor() #
If a table exists Sutdent Delete first cursor.execute("DROP TABLE IF EXISTS Student") sql = """CREATE
TABLE Student ( ID CHAR(10) NOT NULL, Name CHAR(8), Grade INT )""" # establish Sutdent surface
cursor.execute(sql) def insertdb(db): # use cursor() 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: # implement sql 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): # use cursor() Method get operation cursor
cursor = db.cursor() # SQL Query statement #sql = "SELECT * FROM Student \ # WHERE Grade >
'%d'" % (80) sql = "SELECT * FROM Student" try: # implement SQL 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): #
use cursor() Method get operation cursor cursor = db.cursor() # SQL Delete statement sql = "DELETE FROM Student
WHERE Grade = '%d'" % (100) try: # implement SQL sentence cursor.execute(sql) # Submit changes
db.commit() except: print ' Failed to delete data !' # Rollback on error db.rollback() def updatedb(db): #
use cursor() Method get operation cursor cursor = db.cursor() # SQL UPDATE statement sql = "UPDATE Student SET
Grade = Grade + 3 WHERE ID = '%s'" % ('003') try: # implement SQL 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() # connect MySQL 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 exercise   

Exercises :
        Reference table structure :
            customer type

            User information

            jurisdiction

            customer type & jurisdiction
        function :

            # land , register , Retrieve password
            # user management
            # customer type
            # Rights management
            # Assign permissions

        special : Program only one executable

 

 python python operation SQLite

  SQLite Is an embedded database , Its database is a file . because SQLite In itself C Written , And it's very small . therefore , Often integrated into various applications , Even in iOS and
Android Of APP Can be integrated in .

  Python It's built in SQLite3, So in python Used in SQLite, No need to install anything , Direct use .

   in use SQLite 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 becomes Connection;

   After connecting to the database , Cursor needs to be opened , be called Cursor, adopt Cursor implement SQL sentence , Then get the execution results .

  Python It defines a set of operation database API Interface , Any database to connect to Python, Only compliance is required Python Standard database driver .

   because SQLite The driver is built into the Python In standard library , So we can operate it directly SQLite data base .

   We are Python Interactive command line practice :
# Import SQLite drive : >>> import sqlite3 # connection to SQLite data base # The database file is test.db #
If the file does not exist , Automatically created in the current directory : >>> conn = sqlite3.connect('test.db') # Create a Cursor: >>>
cursor = conn.cursor() # Execute one SQL sentence , establish user surface : >>> cursor.execute('create table
user (id varchar(20) primary key, name varchar(20))') <sqlite3.Cursor object at
0x10f8aa260> # Continue with one SQL sentence , Insert a record : >>> cursor.execute('insert into user (id,
name) values (\'1\', \'Michael\')') <sqlite3.Cursor object at 0x10f8aa260> #
adopt rowcount Get the number of inserted rows : >>> cursor.rowcount 1 # close Cursor: >>> cursor.close() # Commit transaction :
>>> conn.commit() # close Connection: >>> 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()
   use Python Of DB-API Time , Just make sure connection and cursor object , Remember to close it when you open it , You can use it at ease .

   use cursor Object execution insert,update,delete Statement time , Execution results are rowcount Returns the number of rows affected , You can get the execution results .

   use cursor Object execution select Statement time , adopt featchall() You can get the result set , The result set is a list, Each element is a tuple, Corresponding to one line of record .

   If SQL Statement with parameters , Then you need to pass the parameters to the execute() 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