Python operations use the MySQL database instance code, pythonmysql
Python for MySQL
Configuration
- Win_64
- Ubuntu14.04
- Python3.x
Pip install the pymysql Module
Install pip install pymysql directly using pip
Run the command in cmd directly on win64.
Connect to local database
Connect to the database using the module pymysql
#! /Usr/bin/python # coding = utf-8import pymysql # connect to the local database conn = pymysql. connect (host = 'localhost', port = 3306, user = 'root', passwd = 'a123', db = 'samp _ db1', charset = 'utf8 ') cursor = conn.cursor(export cursor.exe cute ('select * from bigstu') for row in cursor. fetchall (): print (row) # Check cursor.exe cute ('select id, name from bigstu where age> 22') for res in cursor. fetchall (): print (str (res [0]) + "," + res [1]) cursor. close () print ('-- end --')
Output:
(1, 'zhang san', 'male', 24, datetime. date (2017, 3, 29), '123') (6, 'xiaogang ', 'male', 23, datetime. date (2017, 3, 11), '123') (8, 'xiaoxia ', 'female, 20, datetime. date (2017, 3, 13), '20140901') (12, 'smart', 'male', 21, datetime. date (2017, 3, 7), '000000') 1, Zhang san6, Xiaogang -- end --
You can directly execute SQL statements. The result is a tuple.
Add
Insert data
Insert a piece of data and connect the following code
InsertSql = "insert into bigstu (name, sex, age, mobile) values ('% s',' % s', % d, '% s ') "xiuji = ('xiugi', 'male', 15, '13400001111'{cursor.exe cute (insertSql % xiuji) conn. commit () # Don't forget to submit
Add Column
Add a column of cash after mobile
addCo = "alter table bigstu add cash int after mobile"cursor.execute(addCo)
If you want to set the default value
addCo = "alter table bigstu add cash int default 0 after mobile"cursor.execute(addCo)
Delete
Delete data
Delete data with name = xiuji
DeleteSql = "delete from bigstu where name = '% S'" cursor.exe cute (deleteSql % 'xiugi ')
Delete column
Delete cash Columns
dropCo = "alter table bigstu drop cash"cursor.execute(dropCo)
Change
Modify data
Update Qualified Data
UpdateSql = "update bigstu set sex = '% s' where name =' % S'" updateXiuji = ('xiugi', 'xiugi') # The characteristics of xiuji are xiuji cursor.exe cute (updateSql % updateXiuji) conn. commit ()
Transaction Processing
Add cash for a record
Table = "bigstu" addCash = "update" + table + "set cash = cash + '% d' where name =' % S'" lucky = (1000, "xiuji ") try: cursor.exe cute (addCash % lucky) failed t Exception as e: conn. rollback () print ("failed to add money") else: conn. commit ()
It is very convenient to directly execute SQL statements
Code snippet
Add columns to a database
Read the name of the column to be added from json and obtain all the column names in the current two tables.
Sort out the names of the columns to be inserted and insert the columns into the corresponding table.
Import pymysqlimport jsonimport osimport secureUtilsmapping_keys = json. load (open ("key_mapping_db.json", "r") db_keys = [] # All keyfor k in json in mapping_keys.values (): db_keys.append (k) conn = pymysql. connect (host = 'localhost', port = 3306, user = 'root', passwd = '*****', db = 'db _ name ', charset = 'utf8') cursor = conn. cursor () table_main = "table_main" main_table_keys = [] # Name of the primary table, cursor.exe cute ("show columns from" + table_main) for row in cursor. fetchall (): main_table_keys.append (row [0]) staff_table_keys = tables cursor.exe cute ("show columns from table_second") for row in cursor. fetchall (): staff_table_keys.append (row [0]) Keys = [] for k in db_keys: if k not in staff_table_keys and k not in main_table_keys and k not in keys: Keys (k) print ("need to insert" + str (len (need_to_insert_keys) print (need_to_insert_keys) for kn in need_to_insert_keys: print ("add key to db" + kn) cursor.exe cute ("alter table staff_table add" + kn + "text") conn. close ()
Change Field characters
Change all fields in main_table_keys to utf8.
# change column character set to utf8for co in main_table_keys: change_sql = "alter table " + table_main + " modify " + co + " text character set utf8" print(change_sql) cursor.execute(change_sql)
The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.