How do I connect to a MySQL Database in Python

Question

How do I connect to a MySQL database using a python program

User · Answer

Despite all answers above  in case you do not want to connect to a specific database upfront  for example  if you want to create the database still      you can use connection select db database   as demonstrated in the following   import pymysql cursors connection   pymysql connect host  localhost                            user  mahdi                            password  mahdi                            charset  utf8mb4                            cursorclass pymysql cursors DictCursor  cursor   connection cursor   cursor execute  CREATE DATABASE IF NOT EXISTS   database  connection select db database  sql create    CREATE TABLE IF NOT EXISTS   tablename  timestamp DATETIME NOT NULL PRIMARY KEY   cursor execute sql create  connection commit   cursor close

User · Answer

MySQLdb is the straightforward way  You get to execute SQL queries over a connection  Period   My preferred way  which is also pythonic  is to use the mighty SQLAlchemy instead  Here is a query related tutorial  and here is a tutorial on ORM capabilities of SQLALchemy

User · Answer

This is Mysql DB connection   from flask import Flask  render template  request from flask mysqldb import MySQL  app   Flask   name      app config  MYSQL HOST      localhost  app config  MYSQL USER      root  app config  MYSQL PASSWORD      root  app config  MYSQL DB      MyDB   mysql   MySQL app     app route      methods   GET    POST     def index        if request method     POST           details   request form         cur   mysql connection cursor           cur execute    Your query            mysql connection commit           cur close           return  success      return render template  index html     if   name         main         app run

User · Answer

Try using MySQLdb  MySQLdb only supports Python 2   There is a how to page here  http   www kitebird com articles pydbapi html    From the page     server version py - retrieve and display database server version  import MySQLdb  conn   MySQLdb connect  host    localhost                           user    testuser                           passwd    testpass                           db    test   cursor   conn cursor    cursor execute   SELECT VERSION     row   cursor fetchone    print  server version    row 0  cursor close    conn close

User · Answer

First  install python-mysql connector from https   dev mysql com downloads connector python   on Python console enter     pip install mysql-connector-python-rf import mysql connector

User · Answer

Connecting to MYSQL with Python 2 in three steps  1 - Setting  You must install a MySQL driver before doing anything  Unlike PHP  Only the SQLite driver is installed by default with Python  The most used package to do so is MySQLdb but it s hard to install it using easy install  Please note MySQLdb only supports Python 2   For Windows user  you can get an exe of MySQLdb    For Linux  this is a casual package  python-mysqldb    You can use sudo apt-get install python-mysqldb  for debian based distros   yum install MySQL-python  for rpm-based   or dnf install python-mysql  for modern fedora distro  in command line to download    For Mac  you can install MySQLdb using Macport   2 - Usage  After installing  Reboot  This is not mandatory  But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong  So please reboot   Then it is just like using any other package       usr bin python import MySQLdb  db   MySQLdb connect host  localhost        your host  usually localhost                      user  john             your username                      passwd  megajonhy      your password                      db  jonhydb            name of the data base    you must create a Cursor object  It will let    you execute all the queries you need cur   db cursor      Use all the SQL you like cur execute  SELECT   FROM YOUR TABLE NAME      print all the first cell of all the rows for row in cur fetchall        print row 0   db close     Of course  there are thousand of possibilities and options  this is a very basic example  You will have to look at the documentation  A good starting point   3 - More advanced usage  Once you know how it works  You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects  The most famous ORM in the Python community is SQLAlchemy    I strongly advise you to use it  your life is going to be much easier   I recently discovered another jewel in the Python world  peewee  It s a very lite ORM  really easy and fast to setup then use  It makes my day for small projects or stand alone apps  Where using big tools like SQLAlchemy or Django is overkill    import peewee from peewee import    db   MySQLDatabase  jonhydb   user  john   passwd  megajonhy    class Book peewee Model       author   peewee CharField       title   peewee TextField        class Meta          database   db  Book create table   book   Book author  me   title  Peewee is cool   book save   for book in Book filter author  me        print book title   This example works out of the box  Nothing other than having peewee  pip install peewee  is required

User · Answer

If you do not need MySQLdb  but would accept any library  I would very  very much recommend MySQL Connector Python from MySQL  http   dev mysql com downloads connector python    It is one package  around 110k   pure Python  so it is system independent  and dead simple to install  You just download  double-click  confirm license agreement and go  There is no need for Xcode  MacPorts  compiling  restarting      Then you connect like   import mysql connector     cnx   mysql connector connect user  scott   password  tiger                                 host  127 0 0 1                                 database  employees    try     cursor   cnx cursor      cursor execute           select 3 from your table            result   cursor fetchall      print result finally      cnx close

User · Answer

First install the driver  Ubuntu    sudo apt-get install python-pip sudo pip install -U pip sudo apt-get install python-dev libmysqlclient-dev sudo apt-get install MySQL-python   MySQL database connection codes  import MySQLdb conn   MySQLdb connect  host    localhost  user    root  passwd    pass  db    dbname   cursor   conn cursor    cursor execute   SELECT VERSION     row   cursor fetchone    print  server version    row 0  cursor close    conn close

User · Answer

you can connect your python code to mysql in this way    import MySQLdb db   MySQLdb connect host  localhost                    user  appuser                    passwd                      db  onco    cursor   db cursor

User · Answer

For python 3 3  CyMySQL https   github com nakagami CyMySQL  I have pip installed on my windows 7  just  pip install cymysql   you don t need cython  quick and painless

User · Answer

PyMySQL 0 10 1 - Released  Sep 10  2020  has support for python3 as well  python3 -m pip install PyMySQL  Simple code  import pymysql    Connect to the database conn   pymysql connect host  127 0 0 1  user  root  passwd  root  db  fax      Create a Cursor object cur   conn cursor      Execute the query cur execute  quot SELECT   FROM fax student quot      Read and print records for row in cur fetchall        print row   output   1   Petar    Petrovic   1813   Njegusi    2   Donald    Tramp   1946   New York    3   Bill    Gates   1955   Seattle

User · Answer

Here s one way to do it  using MySQLdb  which only supports Python 2      usr bin python import MySQLdb    Connect db   MySQLdb connect host  localhost                        user  appuser                        passwd                          db  onco    cursor   db cursor      Execute SQL select statement cursor execute  SELECT   FROM location      Commit your changes if writing   In this case  we are only reading data   db commit      Get the number of rows in the resultset numrows   cursor rowcount    Get and display one row at a time for x in range 0  numrows       row   cursor fetchone       print row 0    -- gt    row 1     Close the connection db close     Reference here

User · Answer

Oracle  MySQL  now supports a pure Python connector   That means no binaries to install  it s just a Python library   It s called  Connector Python      http   dev mysql com downloads connector python

User · Answer

First install the driver  Ubuntu    sudo apt-get install python-pip sudo pip install -U pip sudo apt-get install python-dev libmysqlclient-dev sudo apt-get install MySQL-python   MySQL database connection codes  import MySQLdb conn   MySQLdb connect  host    localhost  user    root  passwd    pass  db    dbname   cursor   conn cursor    cursor execute   SELECT VERSION     row   cursor fetchone    print  server version    row 0  cursor close    conn close

User · Answer

Just a modification in above answer   Simply run this command to install mysql for python  sudo yum install MySQL-python sudo apt-get install MySQL-python   remember  It is case sensitive

User · Answer

for Python3 6 I found two driver  pymysql and mysqlclient  I tested the performance between them and got the result  the mysqlclient is faster   below is my test process need install python lib profilehooks to analyze time elapse   raw sql  select   from FOO   immediatly execute in mysql terminal  46410 rows in set  0 10 sec   pymysql  2 4s    from profilehooks import profile import pymysql cursors import pymysql connection   pymysql connect host  localhost   user  root   db  foo   c   connection cursor     profile immediate True  def read by pymysql        c execute  select   from FOO        res   c fetchall    read by pymysql     here s the pymysql profile     mysqlclient  0 4s   from profilehooks import profile import MySQLdb  connection   MySQLdb connect host  localhost   user  root   db  foo   c   connection cursor     profile immediate True  def read by mysqlclient        c execute  select   from FOO        res   c fetchall    read by mysqlclient     here s the mysqlclient profile    So  it seems that mysqlclient is much faster than pymysql

User · Answer

Try using MySQLdb  MySQLdb only supports Python 2   There is a how to page here  http   www kitebird com articles pydbapi html    From the page     server version py - retrieve and display database server version  import MySQLdb  conn   MySQLdb connect  host    localhost                           user    testuser                           passwd    testpass                           db    test   cursor   conn cursor    cursor execute   SELECT VERSION     row   cursor fetchone    print  server version    row 0  cursor close    conn close

User · Answer

If you do not need MySQLdb  but would accept any library  I would very  very much recommend MySQL Connector Python from MySQL  http   dev mysql com downloads connector python    It is one package  around 110k   pure Python  so it is system independent  and dead simple to install  You just download  double-click  confirm license agreement and go  There is no need for Xcode  MacPorts  compiling  restarting      Then you connect like   import mysql connector     cnx   mysql connector connect user  scott   password  tiger                                 host  127 0 0 1                                 database  employees    try     cursor   cnx cursor      cursor execute           select 3 from your table            result   cursor fetchall      print result finally      cnx close

User · Answer

for Python3 6 I found two driver  pymysql and mysqlclient  I tested the performance between them and got the result  the mysqlclient is faster   below is my test process need install python lib profilehooks to analyze time elapse   raw sql  select   from FOO   immediatly execute in mysql terminal  46410 rows in set  0 10 sec   pymysql  2 4s    from profilehooks import profile import pymysql cursors import pymysql connection   pymysql connect host  localhost   user  root   db  foo   c   connection cursor     profile immediate True  def read by pymysql        c execute  select   from FOO        res   c fetchall    read by pymysql     here s the pymysql profile     mysqlclient  0 4s   from profilehooks import profile import MySQLdb  connection   MySQLdb connect host  localhost   user  root   db  foo   c   connection cursor     profile immediate True  def read by mysqlclient        c execute  select   from FOO        res   c fetchall    read by mysqlclient     here s the mysqlclient profile    So  it seems that mysqlclient is much faster than pymysql

User · Answer

First step to get The Library  Open terminal and execute pip install mysql-python-connector  After the installation go the second step  Second Step to import the library  Open your python file and write the following code  import mysql connector Third step to connect to the server  Write the following code   conn   mysql connector connect host you host name like  localhost or 127 0 0 1  username your  username like root  password   your  password   Third step Making the cursor  Making a cursor makes it easy for us to run queries  To make the cursor use the following code  cursor   conn cursor   Executing queries  For executing queries you can do the following  cursor execute query  If the query changes any thing in the table you need to add the following code after the execution of the query  conn commit   Getting values from a query  If you want to get values from a query then you can do the following  cursor excecute  SELECT   FROM table name   for i in cursor  print i   Or for i in cursor fetchall    print i  The fetchall   method returns a list with many tuples that contain the values that you requested  row after row   Closing the connection  To close the connection you should use the following code  conn close   Handling exception  To Handel exception you can do it Vai the following method  try   Logic pass except mysql connector errors Error   Logic  pass To use a database  For example you are a account creating system where you are storing the data in a database named blabla  you can just add a database parameter to the connect   method  like mysql connector connect database   database name  don t remove other informations like host username password

User · Answer

Here s one way to do it  using MySQLdb  which only supports Python 2      usr bin python import MySQLdb    Connect db   MySQLdb connect host  localhost                        user  appuser                        passwd                          db  onco    cursor   db cursor      Execute SQL select statement cursor execute  SELECT   FROM location      Commit your changes if writing   In this case  we are only reading data   db commit      Get the number of rows in the resultset numrows   cursor rowcount    Get and display one row at a time for x in range 0  numrows       row   cursor fetchone       print row 0    -- gt    row 1     Close the connection db close     Reference here

User · Answer

First  install python-mysql connector from https   dev mysql com downloads connector python   on Python console enter     pip install mysql-connector-python-rf import mysql connector

User · Answer

Here s one way to do it  using MySQLdb  which only supports Python 2      usr bin python import MySQLdb    Connect db   MySQLdb connect host  localhost                        user  appuser                        passwd                          db  onco    cursor   db cursor      Execute SQL select statement cursor execute  SELECT   FROM location      Commit your changes if writing   In this case  we are only reading data   db commit      Get the number of rows in the resultset numrows   cursor rowcount    Get and display one row at a time for x in range 0  numrows       row   cursor fetchone       print row 0    -- gt    row 1     Close the connection db close     Reference here

User · Answer

Oracle  MySQL  now supports a pure Python connector   That means no binaries to install  it s just a Python library   It s called  Connector Python      http   dev mysql com downloads connector python

User · Answer

Best way to connect to MySQL from python is to Use MySQL Connector Python because it is official Oracle driver for MySQL for working with Python and it works with both Python 3 and Python 2   follow the steps mentioned below to connect MySQL   install connector using pip   pip install mysql-connector-python   or you can download the installer from https   dev mysql com downloads connector python    Use connect   method of mysql connector python to connect to MySQL pass the required argument to connect   method  i e  Host  username  password  and database name  Create cursor object from connection object returned by connect  method to execute SQL queries  close the connection after your work completes    Example   import mysql connector  from mysql connector import Error  try       conn   mysql connector connect host  hostname                            database  db                            user  root                            password  passcode        if conn is connected           cursor   conn cursor          cursor execute  select database             record   cursor fetchall          print   You re connected to -    record   except Error as e       print   Print your error msg   e   finally       closing database connection      if conn is connected            cursor close          conn close     Reference  - https   pynative com python-mysql-database-connection   Important API of MySQL Connector Python   For DML operations - Use cursor execute   and cursor executemany   to run query  and after this use connection commit   to persist your changes to DB To fetch data      - Use cursor execute   to run query and cursor fetchall    cursor fetchone    cursor fetchmany SIZE  to fetch data

User · Answer

Even though some of you may mark this as a duplicate and get upset that I am copying someone else s answer  I would REALLY like to highlight an aspect of Mr  Napik s response    Because I missed this  I caused nationwide website downtime  9min    If only someone shared this information  I could have prevented it    Here is his code   import mysql connector     cnx   mysql connector connect user  scott   password  tiger                                 host  127 0 0 1                                 database  employees   try     cursor   cnx cursor      cursor execute    select 3 from your table        result   cursor fetchall      print result  finally      cnx close     The important thing here is the Try and Finally clause   This allows connections to ALWAYS be closed  regardless of what happens in the cursor sqlstatement portion of the code   A lot of active connections cause DBLoadNoCPU to spike and could crash a db server     I hope this warning helps to save servers and ultimately jobs   D

User · Answer

first install the driver  pip install MySQL-python      Then a basic code goes like this       usr bin python import MySQLdb  try      db   MySQLdb connect host  localhost          db server  can be a remote one                       db  mydb                     database                      user  mydb                   username                      passwd  mydb123              password for this username                                       Create a Cursor object     cur   db cursor          Create a query string  It can contain variables     query string    SELECT   FROM MY TABLE         Execute the query     cur execute query string         Get all the rows present the database     for each row in cur fetchall            print each row        Close the connection     db close   except Exception  e      print  Error    e

User · Answer

Best way to connect to MySQL from python is to Use MySQL Connector Python because it is official Oracle driver for MySQL for working with Python and it works with both Python 3 and Python 2   follow the steps mentioned below to connect MySQL   install connector using pip   pip install mysql-connector-python   or you can download the installer from https   dev mysql com downloads connector python    Use connect   method of mysql connector python to connect to MySQL pass the required argument to connect   method  i e  Host  username  password  and database name  Create cursor object from connection object returned by connect  method to execute SQL queries  close the connection after your work completes    Example   import mysql connector  from mysql connector import Error  try       conn   mysql connector connect host  hostname                            database  db                            user  root                            password  passcode        if conn is connected           cursor   conn cursor          cursor execute  select database             record   cursor fetchall          print   You re connected to -    record   except Error as e       print   Print your error msg   e   finally       closing database connection      if conn is connected            cursor close          conn close     Reference  - https   pynative com python-mysql-database-connection   Important API of MySQL Connector Python   For DML operations - Use cursor execute   and cursor executemany   to run query  and after this use connection commit   to persist your changes to DB To fetch data      - Use cursor execute   to run query and cursor fetchall    cursor fetchone    cursor fetchmany SIZE  to fetch data

User · Answer

This is Mysql DB connection   from flask import Flask  render template  request from flask mysqldb import MySQL  app   Flask   name      app config  MYSQL HOST      localhost  app config  MYSQL USER      root  app config  MYSQL PASSWORD      root  app config  MYSQL DB      MyDB   mysql   MySQL app     app route      methods   GET    POST     def index        if request method     POST           details   request form         cur   mysql connection cursor           cur execute    Your query            mysql connection commit           cur close           return  success      return render template  index html     if   name         main         app run

User · Answer

Also take a look at Storm  It is a simple SQL mapping tool which allows you to easily edit and create SQL entries without writing the queries   Here is a simple example   from storm locals import      User will be the mapped object  you have to create the table before mapping it class User object             storm table      user    table name         ID   Int primary True   field ID         name  Unicode     field name  database   create database  mysql   root password localhost 3306 databaseName   store   Store database   user   User   user name   u Mark   print str user ID    None  store add user    store flush     ID is AUTO INCREMENT  print str user ID    1  ID   store commit     commit all changes to the database   To find and object use   michael   store find User  User name    u Michael   one   print str user ID    10   Find with primary key   print store get User  1  name  Mark   For further information see the tutorial

User · Answer

Just a modification in above answer   Simply run this command to install mysql for python  sudo yum install MySQL-python sudo apt-get install MySQL-python   remember  It is case sensitive

User · Answer

MySQLdb is the straightforward way  You get to execute SQL queries over a connection  Period   My preferred way  which is also pythonic  is to use the mighty SQLAlchemy instead  Here is a query related tutorial  and here is a tutorial on ORM capabilities of SQLALchemy

User · Answer

Try using MySQLdb  MySQLdb only supports Python 2   There is a how to page here  http   www kitebird com articles pydbapi html    From the page     server version py - retrieve and display database server version  import MySQLdb  conn   MySQLdb connect  host    localhost                           user    testuser                           passwd    testpass                           db    test   cursor   conn cursor    cursor execute   SELECT VERSION     row   cursor fetchone    print  server version    row 0  cursor close    conn close

User · Answer

PyMySQL 0 10 1 - Released  Sep 10  2020  has support for python3 as well  python3 -m pip install PyMySQL  Simple code  import pymysql    Connect to the database conn   pymysql connect host  127 0 0 1  user  root  passwd  root  db  fax      Create a Cursor object cur   conn cursor      Execute the query cur execute  quot SELECT   FROM fax student quot      Read and print records for row in cur fetchall        print row   output   1   Petar    Petrovic   1813   Njegusi    2   Donald    Tramp   1946   New York    3   Bill    Gates   1955   Seattle

User · Answer

SqlAlchemy       SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that   gives application developers the full power and flexibility of SQL    SQLAlchemy provides a full suite of well known enterprise-level   persistence patterns  designed for efficient and high-performing   database access  adapted into a simple and Pythonic domain language    Installation  pip install sqlalchemy   RAW query  from sqlalchemy import create engine from sqlalchemy orm import sessionmaker  scoped session  engine   create engine  mysql    lt user name gt   lt password gt   lt host name gt   lt db name gt    session obj   sessionmaker bind engine  session   scoped session session obj     insert into database session execute  insert into person values 2   random name     session flush   session commit     ORM way  from sqlalchemy import Column  Integer  String from sqlalchemy ext declarative import declarative base from sqlalchemy import create engine from sqlalchemy orm import sessionmaker  scoped session  Base   declarative base   engine   create engine  mysql    lt user name gt   lt password gt   lt host name gt   lt db name gt    session obj   sessionmaker bind engine  session   scoped session session obj     Bind the engine to the metadata of the Base class so that the   declaratives can be accessed through a DBSession instance Base metadata bind   engine  class Person Base         tablename      person        Here we define columns for the table person       Notice that each column is also a normal Python instance attribute      id   Column Integer  primary key True      name   Column String 250   nullable False     insert into database person obj   Person id 12  name  name   session add person obj  session flush   session commit

User · Answer

A very good connector for MySQL and MariaDB is mysql-connector-python  It is written in Python so you can modify it whenever you want  You can install it by running these commands  pip3 uninstall mysql-connector pip3 install mysql-connector-python   Then you can use it with the following code  import mysql connector  database   mysql connector connect      host  localhost       user  db username      password  passwd       database  db name       autocommit True   cursor   database cursor    cursor execute  SELECT   FROM users     You can pass the autocommit parameter and set it to True  In this way you will not have to call database commit   every time you update the database  Also you can prevent some errors I have mentioned in another question   External links  Python MySQL Connector SQL  Structured Query Language

User · Answer

First step to get The Library  Open terminal and execute pip install mysql-python-connector  After the installation go the second step  Second Step to import the library  Open your python file and write the following code  import mysql connector Third step to connect to the server  Write the following code   conn   mysql connector connect host you host name like  localhost or 127 0 0 1  username your  username like root  password   your  password   Third step Making the cursor  Making a cursor makes it easy for us to run queries  To make the cursor use the following code  cursor   conn cursor   Executing queries  For executing queries you can do the following  cursor execute query  If the query changes any thing in the table you need to add the following code after the execution of the query  conn commit   Getting values from a query  If you want to get values from a query then you can do the following  cursor excecute  SELECT   FROM table name   for i in cursor  print i   Or for i in cursor fetchall    print i  The fetchall   method returns a list with many tuples that contain the values that you requested  row after row   Closing the connection  To close the connection you should use the following code  conn close   Handling exception  To Handel exception you can do it Vai the following method  try   Logic pass except mysql connector errors Error   Logic  pass To use a database  For example you are a account creating system where you are storing the data in a database named blabla  you can just add a database parameter to the connect   method  like mysql connector connect database   database name  don t remove other informations like host username password

User · Answer

Run this command in your terminal to install mysql connector   pip install mysql-connector-python   And run this in your python editor to connect to MySQL   import mysql connector  mydb   mysql connector connect        host  localhost         user  yusername         passwd  password         database  database name      Samples to execute MySQL Commands  in your python edior    mycursor   mydb cursor   mycursor execute  CREATE TABLE customers  name VARCHAR 255   address VARCHAR 255         mycursor execute  SHOW TABLES    mycursor execute  INSERT INTO customers  name  address  VALUES   John    Highway 21         mydb commit     Use this command after insert or update   For more commands  https   www w3schools com python python mysql getstarted asp

User · Answer

As a db driver  there is also oursql  Some of the reasons listed on that link  which say why oursql is better         oursql has real parameterization  sending the SQL and data to MySQL completely separately    oursql allows text or binary data to be streamed into the database and streamed out of the database  instead of requiring everything to be buffered in the client    oursql can both insert rows lazily and fetch rows lazily    oursql has unicode support on by default    oursql supports python 2 4 through 2 7 without any deprecation warnings on 2 6   see PEP 218  and without completely failing on 2 7  see PEP 328     oursql runs natively on python 3 x       So how to connect to mysql with oursql   Very similar to mysqldb   import oursql  db connection   oursql connect host  127 0 0 1  user  foo  passwd  foobar  db  db name   cur db connection cursor   cur execute  SELECT   FROM  tbl name    for row in cur fetchall        print row 0    The tutorial in the documentation is pretty decent   And of course for ORM SQLAlchemy is a good choice  as already mentioned in the other answers

User · Answer

Try using MySQLdb  MySQLdb only supports Python 2   There is a how to page here  http   www kitebird com articles pydbapi html    From the page     server version py - retrieve and display database server version  import MySQLdb  conn   MySQLdb connect  host    localhost                           user    testuser                           passwd    testpass                           db    test   cursor   conn cursor    cursor execute   SELECT VERSION     row   cursor fetchone    print  server version    row 0  cursor close    conn close

User · Answer

A very good connector for MySQL and MariaDB is mysql-connector-python  It is written in Python so you can modify it whenever you want  You can install it by running these commands  pip3 uninstall mysql-connector pip3 install mysql-connector-python   Then you can use it with the following code  import mysql connector  database   mysql connector connect      host  localhost       user  db username      password  passwd       database  db name       autocommit True   cursor   database cursor    cursor execute  SELECT   FROM users     You can pass the autocommit parameter and set it to True  In this way you will not have to call database commit   every time you update the database  Also you can prevent some errors I have mentioned in another question   External links  Python MySQL Connector SQL  Structured Query Language

User · Answer

Connecting to MYSQL with Python 2 in three steps  1 - Setting  You must install a MySQL driver before doing anything  Unlike PHP  Only the SQLite driver is installed by default with Python  The most used package to do so is MySQLdb but it s hard to install it using easy install  Please note MySQLdb only supports Python 2   For Windows user  you can get an exe of MySQLdb    For Linux  this is a casual package  python-mysqldb    You can use sudo apt-get install python-mysqldb  for debian based distros   yum install MySQL-python  for rpm-based   or dnf install python-mysql  for modern fedora distro  in command line to download    For Mac  you can install MySQLdb using Macport   2 - Usage  After installing  Reboot  This is not mandatory  But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong  So please reboot   Then it is just like using any other package       usr bin python import MySQLdb  db   MySQLdb connect host  localhost        your host  usually localhost                      user  john             your username                      passwd  megajonhy      your password                      db  jonhydb            name of the data base    you must create a Cursor object  It will let    you execute all the queries you need cur   db cursor      Use all the SQL you like cur execute  SELECT   FROM YOUR TABLE NAME      print all the first cell of all the rows for row in cur fetchall        print row 0   db close     Of course  there are thousand of possibilities and options  this is a very basic example  You will have to look at the documentation  A good starting point   3 - More advanced usage  Once you know how it works  You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects  The most famous ORM in the Python community is SQLAlchemy    I strongly advise you to use it  your life is going to be much easier   I recently discovered another jewel in the Python world  peewee  It s a very lite ORM  really easy and fast to setup then use  It makes my day for small projects or stand alone apps  Where using big tools like SQLAlchemy or Django is overkill    import peewee from peewee import    db   MySQLDatabase  jonhydb   user  john   passwd  megajonhy    class Book peewee Model       author   peewee CharField       title   peewee TextField        class Meta          database   db  Book create table   book   Book author  me   title  Peewee is cool   book save   for book in Book filter author  me        print book title   This example works out of the box  Nothing other than having peewee  pip install peewee  is required

User · Answer

SqlAlchemy       SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that   gives application developers the full power and flexibility of SQL    SQLAlchemy provides a full suite of well known enterprise-level   persistence patterns  designed for efficient and high-performing   database access  adapted into a simple and Pythonic domain language    Installation  pip install sqlalchemy   RAW query  from sqlalchemy import create engine from sqlalchemy orm import sessionmaker  scoped session  engine   create engine  mysql    lt user name gt   lt password gt   lt host name gt   lt db name gt    session obj   sessionmaker bind engine  session   scoped session session obj     insert into database session execute  insert into person values 2   random name     session flush   session commit     ORM way  from sqlalchemy import Column  Integer  String from sqlalchemy ext declarative import declarative base from sqlalchemy import create engine from sqlalchemy orm import sessionmaker  scoped session  Base   declarative base   engine   create engine  mysql    lt user name gt   lt password gt   lt host name gt   lt db name gt    session obj   sessionmaker bind engine  session   scoped session session obj     Bind the engine to the metadata of the Base class so that the   declaratives can be accessed through a DBSession instance Base metadata bind   engine  class Person Base         tablename      person        Here we define columns for the table person       Notice that each column is also a normal Python instance attribute      id   Column Integer  primary key True      name   Column String 250   nullable False     insert into database person obj   Person id 12  name  name   session add person obj  session flush   session commit

User · Answer

Also take a look at Storm  It is a simple SQL mapping tool which allows you to easily edit and create SQL entries without writing the queries   Here is a simple example   from storm locals import      User will be the mapped object  you have to create the table before mapping it class User object             storm table      user    table name         ID   Int primary True   field ID         name  Unicode     field name  database   create database  mysql   root password localhost 3306 databaseName   store   Store database   user   User   user name   u Mark   print str user ID    None  store add user    store flush     ID is AUTO INCREMENT  print str user ID    1  ID   store commit     commit all changes to the database   To find and object use   michael   store find User  User name    u Michael   one   print str user ID    10   Find with primary key   print store get User  1  name  Mark   For further information see the tutorial

User · Answer

mysqlclient is the best as others only provide support to specific versions of python    pip install mysqlclient   example code      import mysql connector     import  mysql     db  mysql connect  127 0 0 1   root   umer   sys        db  mysql connect host user password db        Example of how to insert new values      db query    INSERT INTO table1 VALUES   01    myname           db store result       db query  SELECT   FROM new1 table1           new1 is scheme table1 is table mysql      res  db store result       for i in range res num rows             print result fetch row      see https   github com PyMySQL mysqlclient-python

User · Answer

Run this command in your terminal to install mysql connector   pip install mysql-connector-python   And run this in your python editor to connect to MySQL   import mysql connector  mydb   mysql connector connect        host  localhost         user  yusername         passwd  password         database  database name      Samples to execute MySQL Commands  in your python edior    mycursor   mydb cursor   mycursor execute  CREATE TABLE customers  name VARCHAR 255   address VARCHAR 255         mycursor execute  SHOW TABLES    mycursor execute  INSERT INTO customers  name  address  VALUES   John    Highway 21         mydb commit     Use this command after insert or update   For more commands  https   www w3schools com python python mysql getstarted asp

User · Answer

As a db driver  there is also oursql  Some of the reasons listed on that link  which say why oursql is better         oursql has real parameterization  sending the SQL and data to MySQL completely separately    oursql allows text or binary data to be streamed into the database and streamed out of the database  instead of requiring everything to be buffered in the client    oursql can both insert rows lazily and fetch rows lazily    oursql has unicode support on by default    oursql supports python 2 4 through 2 7 without any deprecation warnings on 2 6   see PEP 218  and without completely failing on 2 7  see PEP 328     oursql runs natively on python 3 x       So how to connect to mysql with oursql   Very similar to mysqldb   import oursql  db connection   oursql connect host  127 0 0 1  user  foo  passwd  foobar  db  db name   cur db connection cursor   cur execute  SELECT   FROM  tbl name    for row in cur fetchall        print row 0    The tutorial in the documentation is pretty decent   And of course for ORM SQLAlchemy is a good choice  as already mentioned in the other answers

User · Answer

you can connect your python code to mysql in this way    import MySQLdb db   MySQLdb connect host  localhost                    user  appuser                    passwd                      db  onco    cursor   db cursor

User · Answer

Here s one way to do it  using MySQLdb  which only supports Python 2      usr bin python import MySQLdb    Connect db   MySQLdb connect host  localhost                        user  appuser                        passwd                          db  onco    cursor   db cursor      Execute SQL select statement cursor execute  SELECT   FROM location      Commit your changes if writing   In this case  we are only reading data   db commit      Get the number of rows in the resultset numrows   cursor rowcount    Get and display one row at a time for x in range 0  numrows       row   cursor fetchone       print row 0    -- gt    row 1     Close the connection db close     Reference here

User · Answer

For python 3 3  CyMySQL https   github com nakagami CyMySQL  I have pip installed on my windows 7  just  pip install cymysql   you don t need cython  quick and painless

User · Answer

Despite all answers above  in case you do not want to connect to a specific database upfront  for example  if you want to create the database still      you can use connection select db database   as demonstrated in the following   import pymysql cursors connection   pymysql connect host  localhost                            user  mahdi                            password  mahdi                            charset  utf8mb4                            cursorclass pymysql cursors DictCursor  cursor   connection cursor   cursor execute  CREATE DATABASE IF NOT EXISTS   database  connection select db database  sql create    CREATE TABLE IF NOT EXISTS   tablename  timestamp DATETIME NOT NULL PRIMARY KEY   cursor execute sql create  connection commit   cursor close

User · Answer

Stop Using MySQLDb if you want to avoid installing mysql headers just to access mysql from python    Use pymysql  It does all of what MySQLDb does  but it was implemented purely in Python with NO External Dependencies   This makes the installation process on all operating systems consistent and easy   pymysql is a drop in replacement for MySQLDb and IMHO there is no reason to ever use MySQLDb for anything    EVER  - PTSD from installing MySQLDb on Mac OSX and  Nix systems  but that s just me   Installation  pip install pymysql     That s it    you are ready to play    Example usage from pymysql Github repo  import pymysql cursors import pymysql    Connect to the database connection   pymysql connect host  localhost                                user  user                                password  passwd                                db  db                                charset  utf8mb4                                cursorclass pymysql cursors DictCursor   try      with connection cursor   as cursor            Create a new record         sql    INSERT INTO  users    email    password   VALUES   s   s           cursor execute sql    webmaster python org    very-secret           connection is not autocommit by default  So you must commit to save       your changes      connection commit        with connection cursor   as cursor            Read a single record         sql    SELECT  id    password  FROM  users  WHERE  email   s          cursor execute sql    webmaster python org             result   cursor fetchone           print result  finally      connection close        ALSO - Replace MySQLdb in existing code quickly and transparently   If you have existing code that uses MySQLdb  you can easily replace it with pymysql using this simple process     import MySQLdb  lt  lt  Remove this line and replace with  import pymysql pymysql install as MySQLdb     All subsequent references to MySQLdb will use pymysql transparently

User · Answer

Even though some of you may mark this as a duplicate and get upset that I am copying someone else s answer  I would REALLY like to highlight an aspect of Mr  Napik s response    Because I missed this  I caused nationwide website downtime  9min    If only someone shared this information  I could have prevented it    Here is his code   import mysql connector     cnx   mysql connector connect user  scott   password  tiger                                 host  127 0 0 1                                 database  employees   try     cursor   cnx cursor      cursor execute    select 3 from your table        result   cursor fetchall      print result  finally      cnx close     The important thing here is the Try and Finally clause   This allows connections to ALWAYS be closed  regardless of what happens in the cursor sqlstatement portion of the code   A lot of active connections cause DBLoadNoCPU to spike and could crash a db server     I hope this warning helps to save servers and ultimately jobs   D

User · Answer

mysqlclient is the best as others only provide support to specific versions of python    pip install mysqlclient   example code      import mysql connector     import  mysql     db  mysql connect  127 0 0 1   root   umer   sys        db  mysql connect host user password db        Example of how to insert new values      db query    INSERT INTO table1 VALUES   01    myname           db store result       db query  SELECT   FROM new1 table1           new1 is scheme table1 is table mysql      res  db store result       for i in range res num rows             print result fetch row      see https   github com PyMySQL mysqlclient-python

User · Answer

MySQLdb is the straightforward way  You get to execute SQL queries over a connection  Period   My preferred way  which is also pythonic  is to use the mighty SQLAlchemy instead  Here is a query related tutorial  and here is a tutorial on ORM capabilities of SQLALchemy

User · Answer

Stop Using MySQLDb if you want to avoid installing mysql headers just to access mysql from python    Use pymysql  It does all of what MySQLDb does  but it was implemented purely in Python with NO External Dependencies   This makes the installation process on all operating systems consistent and easy   pymysql is a drop in replacement for MySQLDb and IMHO there is no reason to ever use MySQLDb for anything    EVER  - PTSD from installing MySQLDb on Mac OSX and  Nix systems  but that s just me   Installation  pip install pymysql     That s it    you are ready to play    Example usage from pymysql Github repo  import pymysql cursors import pymysql    Connect to the database connection   pymysql connect host  localhost                                user  user                                password  passwd                                db  db                                charset  utf8mb4                                cursorclass pymysql cursors DictCursor   try      with connection cursor   as cursor            Create a new record         sql    INSERT INTO  users    email    password   VALUES   s   s           cursor execute sql    webmaster python org    very-secret           connection is not autocommit by default  So you must commit to save       your changes      connection commit        with connection cursor   as cursor            Read a single record         sql    SELECT  id    password  FROM  users  WHERE  email   s          cursor execute sql    webmaster python org             result   cursor fetchone           print result  finally      connection close        ALSO - Replace MySQLdb in existing code quickly and transparently   If you have existing code that uses MySQLdb  you can easily replace it with pymysql using this simple process     import MySQLdb  lt  lt  Remove this line and replace with  import pymysql pymysql install as MySQLdb     All subsequent references to MySQLdb will use pymysql transparently

User · Answer

first install the driver  pip install MySQL-python      Then a basic code goes like this       usr bin python import MySQLdb  try      db   MySQLdb connect host  localhost          db server  can be a remote one                       db  mydb                     database                      user  mydb                   username                      passwd  mydb123              password for this username                                       Create a Cursor object     cur   db cursor          Create a query string  It can contain variables     query string    SELECT   FROM MY TABLE         Execute the query     cur execute query string         Get all the rows present the database     for each row in cur fetchall            print each row        Close the connection     db close   except Exception  e      print  Error    e

User · Answer

MySQLdb is the straightforward way  You get to execute SQL queries over a connection  Period   My preferred way  which is also pythonic  is to use the mighty SQLAlchemy instead  Here is a query related tutorial  and here is a tutorial on ORM capabilities of SQLALchemy

[python] How do I connect to a MySQL Database in Python?

Examples related to python

Examples related to mysql