Python equivalent of PHP mysql_fetch_array

I would like to fetch an array in MySQL. Can someone please tell me how to use Python using MySQLdb to do so?

For example, this is what I would like to do in Python:

<?php

  require_once('Config.php'); 

  $q = mysql_query("SELECT * FROM users WHERE firstname = 'namehere'");
  $data = mysql_fetch_array($q);
  echo $data['lastname'];

?>

Thanks.

Answers


  1. Install MySQLdb (the drivers for MySQL for Python). Type pip install mysql-python
  2. Read up on the Python DB API, which is the standard way to access databases in Python.

Then, try this:

>>> import MySQLdb
>>> connection = MySQLdb.connect(database='test')
>>> cursor = connection.cursor()
>>> cursor.execute('SELECT * FROM users WHERE firstname = %s',('somename',))
>>> results = cursor.fetchall()
>>> for i in results:
       print i

In python you have dictionary=True, I have tested in python3. This returns directory which is much similar to associative array in php. eg.

import mysql.connector
cnx = mysql.connector.connect(user='root', password='',host='127.0.0.1',database='test1')
cursor = cnx.cursor(dictionary=True)
sql= ("SELECT * FROM `users` WHERE id>0")
cursor.execute(sql)
results = cursor.fetchall()
print(results)

I would use SQLAlchemy. Something like this would do the trick:

engine = create_engine('mysql://username:password@host:port/database')
connection = engine.connect()
result = connection.execute("select username from users")
for row in result:
    print "username:", row['username']
connection.close()


You can use this (dictionary=True):

import mysql.connector

db = mysql.connector.connect(user='root', password='',host='127.0.0.1', database='test1')

cursor = db.cursor(dictionary=True)
cursor.execute("SELECT * FROM table")

for row in cursor:
    print(row['column'])

Try:

import MySQLdb
connection = MySQLdb.connect(host="localhost",  # your host
                     user="root",  # username
                     passwd="password",  # password
                     db="frateData")  # name of the database)
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('SELECT * FROM users WHERE firstname = %s',['namehere'])
data = cursor.fetchall()
print data['lastname']

Please note that by initiating your cursor by passing the following parameter: "MySQLdb.cursors.DictCursor" a list instead of an array is returned so you can reference the data with their key name, which in your case in lastname.


Need Your Help

Possible Javascript issue when hosting skin files on CDN

javascript magento cdn

I have recently configured a CDN through Nexcess, our hosting provider, for our Magento store. I originally did this only for media images but recently moved the theme/skin files over the CDN as w...

Informative sentences from words

java android algorithm

I have some words like "Arizona", "Jhon called smith", "Morning","jogging" ,I wanna sentence as"Jhon called smith from Arizona while jogging in morning"