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.
David Moles
51.7k30 gold badges152 silver badges250 bronze badges
asked Jul 19, 2012 at 16:43
Austin K
5492 gold badges5 silver badges13 bronze badges
5 Answers 5
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)
answered Jul 16, 2018 at 15:11
Anup_Tripathi
2,8253 gold badges29 silver badges37 bronze badges
Sign up to request clarification or add additional context in comments.
1 Comment
Jingshao Chen
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'])
Comments
- Install MySQLdb (the drivers for MySQL for Python). Type
pip install mysql-python - 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
answered Jul 19, 2012 at 16:54
Burhan Khalid
175k20 gold badges255 silver badges292 bronze badges
3 Comments
tadman
It is worrying this is the only answer with some form of SQL escaping.
Austin K
Thank you for your help! I just have one more question. When I use this, data is returned in this format: (('dave',), ('jake',)). How can I edit the format of this data, so it is like this ('dave', 'jake'). I am looping through the data that is returned and using each as a variable. This would be much easier to manipulate for my purpose. Thanks again.
Burhan Khalid
names = [i[0] for i in cursor.fetchall()]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()
answered Jul 19, 2012 at 16:58
reptilicus
10.4k6 gold badges59 silver badges80 bronze badges
Comments
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.
answered Feb 9, 2018 at 15:35
Antonis Charalambous
1,0629 silver badges12 bronze badges
Comments
default