Python operations use the MySQL database instance code, pythonmysql

Source: Internet
Author: User

Python operations use the MySQL database instance code, pythonmysql

Python for MySQL

Configuration

  1. Win_64
  2. Ubuntu14.04
  3. 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.

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.