Python mysql connector package

2. MySQL with Python¶

The ‘mysql-connector’ is not supported by Django-framework. The good option is ‘mysqlclient’ which is supported by Django as well.

$ mysql -u root -p Enter password: mysql> CREATE DATABASE pythonSQL; 

2.2. Connect and load data¶

Following code can be used to connect and load the data to database. Note that, the commands in the c.execute(…) statements are exactly same as the commands in the previous chapters.

# create_fill_database.py import mysql.connector as mc # connect to database conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() # cursor to perform operations def create_table(): """ Create table in the database """ # optional: drop table if exists c.execute('DROP TABLE IF EXISTS writer') c.execute('CREATE TABLE writer \ ( \ id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, \ name VARCHAR(30) NOT NULL UNIQUE, \ age int \ )' ) def insert_data(): """ Insert data to the table """ c.execute("INSERT INTO writer (name) VALUES ('Pearl Buck')") c.execute(" INSERT INTO writer VALUES \ (NULL, 'Rabindranath Tagore', 80), \ (NULL, 'Leo Tolstoy', 82)" \ ) c.execute(" INSERT INTO writer (age, name) VALUES \ (30, 'Meher Krishna Patel')" \ ) def commit_close(): """ commit changes to database and close connection """ conn.commit() c.close() conn.close() def main(): """ execute create and insert commands """ create_table() insert_data() commit_close() # required for save the changes # standard boilerplate to call main function if __name__ == '__main__': main() 

$ python create_fill_database.py

2.3. Read data from table¶

Following code can be used to read data from the table,

# read_database.py import mysql.connector as mc conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() def read_data(): c.execute('SELECT * FROM writer') writers = c.fetchall() # data is read in the form of list for writer in writers: # print individual item in the list print(writer) # data at each row is saved as tuple def main(): read_data() if __name__ == '__main__': main() 

$ python read_database.py (1, 'Pearl Buck', None) (2, 'Rabindranath Tagore', 80) (3, 'Leo Tolstoy', 82) (4, 'Meher Krishna Patel', 30) 

• In this way, we can get the data from the table and perform various operations on the data.
• Also, we can use all those queries with python, as queries in the execute statements are same as queries in previous chapter.

2.4. Connection in try-except block¶

We can use following code to put the connection string in the try except block, so that we can get proper message for not connecting with the database,

# connect_try.py import mysql.connector as mq from mysql.connector import errorcode try: conn = mq.connect(host='localhost', user='root', password='d', db='pythonSQL') print("Connected") except mq.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) else: print("Connection closed") conn.close() 

$ python connect_try.py Connected Connection closed

$ python connect_try.py Something is wrong with your user name or password

$ python connect_try.py Database does not exist

© Copyright 2017, Meher Krishna Patel. Revision 31d452b4 .

Versions latest Downloads pdf html epub On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. Documentation & Download: http://dev.mysql.com/doc/connector-python/en

License

mysql/mysql-connector-python

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

…penTelemetry lib license info

Git stats

Files

Failed to load latest commit information.

README.rst

MySQL Connector/Python enables Python programs to access MySQL databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249). It also contains an implementation of the X DevAPI, an Application Programming Interface for working with the MySQL Document Store.

The recommended way to install Connector/Python is via pip.

Make sure you have a recent pip version installed on your system. If your system already has pip installed, you might need to update it. Or you can use the standalone pip installer.

shell> pip install mysql-connector-python

Please refer to the installation tutorial for installation alternatives.

Using the MySQL classic protocol:

import mysql.connector # Connect to server cnx = mysql.connector.connect( host="127.0.0.1", port=3306, user="mike", password="s3cre3t!") # Get a cursor cur = cnx.cursor() # Execute a query cur.execute("SELECT CURDATE()") # Fetch one result row = cur.fetchone() print("Current date is: ".format(row[0])) # Close connection cnx.close()

import mysqlx # Connect to server session = mysqlx.get_session( host="127.0.0.1", port=33060, user="mike", password="s3cr3t!") schema = session.get_schema("test") # Use the collection "my_collection" collection = schema.get_collection("my_collection") # Specify which document to find with Collection.find() result = collection.find("name like :param") \ .bind("param", "S%") \ .limit(1) \ .execute() # Print document docs = result.fetch_all() print(r"Name: ".format(docs[0]["name"])) # Close session session.close()

• MySQL Connector/Python Developer Guide
• MySQL Connector/Python X DevAPI Reference
• MySQL Connector/Python Forum
• MySQL Public Bug Tracker
• Slack (Sign-up required if you do not have an Oracle account)
• Stack Overflow
• InsideMySQL.com Connectors Blog

There are a few ways to contribute to the Connector/Python code. Please refer to the contributing guidelines for additional information.

Please refer to the README.txt and LICENSE.txt files, available in this repository, for further details.

Источник