Pip install mysqldb python

Содержание

MySQL and Python3
Package Installation
The Python MySQL cl.exe Distraction
The Right Package (mysqlclient)
Example CRUD Operations
Create
Retrieve
Update
Delete
Installing MySQLdb for Python 3 in Windows
Problems with installing MySQLdb on Windows
Installing MySQLdb on Windows
Testing MySQLdb Installation
How to install Python MySQLdb module using pip
Join the world’s most active Tech Community!
Welcome back to the World’s most active Tech Community!
Subscribe to our Newsletter, and get personalized recommendations.
TRENDING CERTIFICATION COURSES
TRENDING MASTERS COURSES
COMPANY
WORK WITH US
DOWNLOAD APP
CATEGORIES
CATEGORIES

MySQL and Python3

I’m using Python more and more so it’s not surprising that recently the need arose to connect to and insert into a MySQL database. I was using (and usually do) Python3, and I ran into a couple of issues in the begining. This post will document those issues and the solutions I found to get MySQL and Python3 to work together.

Package Installation

Support for MySQL doesn’t ship with the Python installer, therefore we need to install a package that adds this functionality. I usually use pip3 for this task, and I tried the following.

I found code examples online showing import MySQLdb statements, so I figured that was the name of the package. I was wrong.

Could not find a version that satisfies the requirement MySQLdb (from versions: ) No matching distribution found for MySQLdb

So I thought, let’s try just pip3 install mysql

Collecting mysql Using cached https://files.pythonhosted.org/packages/06/ef/c4efbf2a51fb46aba9be03a973638d9539c9ca10a5259b2cbb1a66133b2e/mysql-0.0.1.tar.gz Collecting MySQL-python (from mysql) Using cached https://files.pythonhosted.org/packages/a5/e9/51b544da85a36a68debe7a7091f068d802fc515a3a202652828c73453cad/MySQL-python-1.2.5.zip Installing collected packages: MySQL-python, mysql Running setup.py install for MySQL-python . error Complete output from command "c:\program files\python36\python.exe" -u -c "import setuptools, tokenize;__file__='C:\\Users\\admin\\AppData\\Local\\Temp\\pip-install-88jce1si\\MySQL-python\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record C:\Users\admin\AppData\Local\Temp\pip-record-f7l848e_\install-record.txt --single-version-externally-managed --compile: running install running build running build_py creating build [omitted output] building '_mysql' extension creating build\temp.win-amd64-3.6 creating build\temp.win-amd64-3.6\Release C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.12.25827\bin\HostX64\x64\cl.exe /c /nologo /Ox /W3 /GL /DNDEBUG /MT -Dversion_info=(1,2,5,'final',1) -D__version__=1.2.5 "-IC:\Program Files (x86)\MySQL\MySQL Connector C 6.0.2\include" "-Ic:\program files\python36\include" "-Ic:\program files\python36\include" "-IC:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.12.25827\Include" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\shared" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\um" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\winrt" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\ucrt" "-IC:\Program Files (x86)\Windows Kits\NETFXSDK\4.6.1\include\um" /Tc_mysql.c /Fobuild\temp.win-amd64-3.6\Release\_mysql.obj /Zl _mysql.c _mysql.c(42): fatal error C1083: Cannot open include file: 'config-win.h': No such file or directory error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2017\\Community\\VC\\Tools\\MSVC\\14.12.25827\\bin\\HostX64\\x64\\cl.exe' failed with exit status 2

Okay this is more like it, but now I’m receiving an error instead of installing a package.

Читайте также: Opening and Closing Tags

The Python MySQL cl.exe Distraction

Searching the error error: command ‘C:\\Program Files (x86)\\Microsoft Visual Studio\\2017\\Community\\VC\\Tools\\MSVC\\14.12.25827\\bin\\HostX64\\x64\\cl.exe’ failed with exit status 2 and I learned that cl.exe is the Visual C++ compiler.

Apparently, the installation of the compiler is done via Visual Studio. I have Visual Studio installed so I loaded up the Visual Studio Installer – the program used to add functionality to VS. Sure enough, I didn’t have the Visual C++ components installed. I started installing it. It was taking awhile because the installation size was over 1 GB. While that was going on I continued to research MySQL and Python3. Before the process finished, I learned that the MySQLdb was not designed for Python3; the functionality was replaced by the mysqlclient package.

The Right Package (mysqlclient)

Now knowing that MySQLdb was out and mysqlclient was in, I went ahead and installed that package.

Collecting mysqlclient Using cached https://files.pythonhosted.org/packages/32/4b/a675941221b6e796efbb48c80a746b7e6fdf7a51757e8051a0bf32114471/mysqlclient-1.3.12-cp36-cp36m-win_amd64.whl Installing collected packages: mysqlclient Successfully installed mysqlclient-1.3.12

Example CRUD Operations

Now that the right MySQL package is installed, let’s do some basic Create, Read, Update, and Delete (CRUD) operations.

Create

The following example opens a CSV file containing records of gold PRICE on a given day ( TIMESTAMP ) from a given SOURCE .

import MySQLdb as mdb PRICE = 0 TIMESTAMP = 1 SOURCE = 2 conn = mdb.connect('hostname', 'username', 'p@s$w0rd', 'database') cursor = conn.cursor() with open('gold_price.csv', 'r') as gold_prices: lines = gold_prices.read().splitlines() for i in range(1, len(lines)): _tuple = lines[i].split(',') cursor.execute("INSERT INTO gold_price (price, time, source) VALUES (%s, '%s', '%s');" % (_tuple[PRICE], _tuple[TIMESTAMP], _tuple[SOURCE])) cursor.close() conn.commit() conn.close()

Retrieve

This bit fetches all the rows in a table (i.e. gold_price ) and prints out each row – as a tuple .

import MySQLdb as mdb conn = mdb.connect('www.jasonfavrod.com', 'econ', 'i8peanut$', 'econ') cursor = conn.cursor() cursor.execute('SELECT * FROM gold_price;') for row in cursor.fetchall(): print(row) cursor.close() conn.close()

# Output (1319.03, 'https://www.xe.com/currencyconverter/convert/?Amount=1&From=XAU&To=USD', datetime.datetime(2018, 5, 12, 19, 30, 2)) (1313.33, 'https://www.xe.com/currencyconverter/convert/?Amount=1&From=XAU&To=USD', datetime.datetime(2018, 5, 14, 21, 0, 3)) (1290.68, 'https://www.xe.com/currencyconverter/convert/?Amount=1&From=XAU&To=USD', datetime.datetime(2018, 5, 15, 21, 0, 3)) [output omited]

Update

Here I’ll update the gold price on a give date to $1300.

import MySQLdb as mdb conn = mdb.connect('www.jasonfavrod.com', 'econ', 'i8peanut$', 'econ') cursor = conn.cursor() cursor.execute("UPDATE gold_price SET price = %s WHERE time = %s", ("1300", "2018.05.15-21:00:03")) cursor.close() conn.commit() conn.close()

Delete

Now we can delete that altered entry.

import MySQLdb as mdb conn = mdb.connect('www.jasonfavrod.com', 'econ', 'i8peanut$', 'econ') cursor = conn.cursor() cursor.execute("DELETE from gold_price WHERE time = '%s'" % "2018-05-23 02:56:07") cursor.close() conn.commit() conn.close()

For more on the mysqlclient for Python3, see their Github project.

Источник

Installing MySQLdb for Python 3 in Windows

My favorite Python connector for MySQL or MariaDB is MySQLdb, the problem with this connector is that it is complicated to install on Windows!

I am creating this article for those who want to install MySQLdb for Python 3 for Windows. Especially me, since each time I am doing a Python project that needs to connect to MariaDB or MySQL I always look on how to install MySQLdb.

If you are interested why I prefer MySQLdb compared to other MySQL connectors you may want to read the comparison of MySQL-connector and MySQLdb from Charles Nagy.

Problems with installing MySQLdb on Windows

You can actually install MySQLdb using pip. See pypi documentation here.

Unfortunately, the pypi documentation is already out of date with the latest release was on Jan 3, 2014.

You can still run the command above but it will look for Microsoft Visual C++ Build Tools, which if you install it in your Windows machine for the purpose of only using MySQLdb will become a program that occupies space but you will never ever use again.

So how do we install MySQLdb for Python on Windows? You can follow the steps below.

Installing MySQLdb on Windows

Download the appropriate .whl for your Python version.

I am currently using Python 3.6.8, so I downloaded mysqlclient‑1.3.13‑cp36‑cp36m‑win_amd64.whl .

On windows command prompt, install the .whl file using pip. – pip install [.whl filename]

In my case the command is

pip install mysqlclient-1.3.13-cp36-cp36m-win_amd64.whl

Note: The file on the command will be different if you use a different Python version or if there is an update on the version of the mysqlclient (MySQLdb).

Once pip says that you have Successfully installed mysqlclient you are good to go on using MySQLdb as your MySQL connector for Python.

Testing MySQLdb Installation

Below is the code I use if my MySQLdb connector has been properly installed in Windows.

import MySQLdb print("Connecting to database using MySQLdb") db_connection = MySQLdb.connect(host='DB_HOST', db='DB_NAME', user='DB_USERNAME', passwd='DB_PASSWORD') print("Succesfully Connected to database using MySQLdb!") db_connection.close()

You can change the following parameters to check if it can connect to your MySQL database or MariaDB database.

If all is well, then when you run the code above it would print Succesfully Connected to database using MySQLdb!