Python mysql fetch array

Python equivalent of PHP mysql_fetch_array-mysql

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()

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.

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']) 

Daniel 195

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) 

More questions

• How to fetch all rows of sql query from MySQL database into array in php
• PHP mysql fetch array error
• Php Email Mysql Fetch Array
• php mysql query fetch array even when no keyword is given
• How to fetch multiple row value which contains multiple value separated by comma into a single array in php using pdo mysql
• How to update MySQL db from two associative array in php
• PHP Array from MYSQL to Javascript Object using JSON
• Php array to MySQL query condition structure
• fetch the names of a the tables in mysql database with pdo and php
• Check if mysql row exisits within foreach loop of php array
• Php mysql dynamic array
• Save MySQL results in 2D array — php
• PHP : create a specific array from MySQL database
• Build a PHP array from a MySQL table
• Fetch data from mysql Foreign key table in php
• Php mysql results as array
• How to grab top 3 from a MySQL Fetch array
• PHP mySql fetch all items from table
• MySQL Equivalent to PHP Explode()
• PHP Mysql select to array format
• Mysql fetch assoc with PHP MYSQL in a table format
• jQuery Autocomplete/Typeahead from MySQL Query to PHP Array
• Fetching An Array With MySQL / PHP
• PHP MySQL Multidimensional Array — Dropdown Menu

More questions with similar tag

• Why is the phpmyadmin search saying there is not unique column?
• Python — MySQLdb — Updating row causes TypeError: not enough arguments for format string
• Show Column if Column not AutoIncrement
• remove tuples within a column with condition
• How to calculate subtraction of 2 Sql Query
• Insert record if not exist without Unique key
• How to print php mysql query response message on front-end
• Update table with chapters, paragraphs and paragraph content using MySql version 8.0.17
• Local and Cloud DB 2 ways replication
• Managing a mysql connection after button clicked
• Switching to PDO, getting an error on submit
• Cannot Connect To Mysql From PhpMyadmin On Centos7
• MySQL code to postgres
• Json becomes invalid when saved to database
• grouping common results in sql
• How to get Banks have High Number of branches first to Low Number of Branch after that In Laravel
• Reverse Engineer MySQL Wworkbench with calculated (generated) fields
• PHP query string with variables in it not working
• Encoding of HTML not declared
• mdb2 error with integer = NULL in prepare statement
• MySQL Join SUM query
• Creating 2 columns with AUTO_INCREMENT on InnoDB
• Google App Engine application connection to a Third Party Cloud
• how to gat data form table1 which does’t contains that id and user name in table2
• Running mySQL queries from a script
• Add new column in SQL query
• Unable to update a single column value in table
• Disable button on conditions
• Cross reference between two tables
• MySQL constraints to force data check on two columns

Источник

Sql fetch data row wise in python mysql

If you can’t use a more usable encoding (utf-8 comes to mind, as it has been designed to handle all unicode characters), you can at least use an alternative error handling like «replace» (replaces non-encodable characters with ‘?’) or «ignore» (suppress non-encodable characters). you can get all of that into a single dataframe and produce the csv. If it takes more time then you can split each chunk into multiple files using the pandas.read_sql as a iterator and then after you did your work consolidate those files into a single one and submit it to postgres.

Fetch one row from MySQL table

The fetchone call is OK. The problem is with your query — when you use a parameterized query, you should not use quotes around the bind variables — these aren’t simple string replacements, but bind variables the database driver handles. Here, you’re querying for a row with the literal name of %s , which probably doesn’t exist. The parameters need to pass to execute as an iterable.

On a related note, you should also remove the quotes around the * in the select list, otherwise, you’ll get the asterisk literal, not all the columns.

sql_select_query = "select * from reinforcement where name = %s" # No quotes --------------^-^-------------------------------^--^ mycursor.execute(sql_select_query, (reinforcement_name,)) # Params passed as a tuple --------^ 

Fastest way to load numeric data into, To transform your result set into an OrderedDict of NumPy arrays, just do this: import turbodbc connection = turbodbc.connect (dsn=»My data source name») cursor = connection.cursor () cursor.execute («SELECT 42») results = cursor.fetchallnumpy () Transforming these results to a dataset should require a …

How to fetch and print utf-8 data from mysql DB using Python?

Your problem is with your terminal ( sys.stdout ) encoding (cf http://en.wikipedia.org/wiki/Code_page_862), which depends on your system’s settings. The best solution (as explained here : https://stackoverflow.com/a/15740694/41316) is to explicitely encode your unicode data before printing them to sys.stdout .

If you can’t use a more usable encoding (utf-8 comes to mind, as it has been designed to handle all unicode characters), you can at least use an alternative error handling like «replace» (replaces non-encodable characters with ‘?’) or «ignore» (suppress non-encodable characters).

Here’s a corrected version of your code, you can play with the encoding and on_error settings to find out what solution works for you:

import sys import MySQLdb # set desired output encoding here # it looks like your default encoding is "cp862" # but you may want to first try 'utf-8' first # encoding = "cp862" encoding = "utf-8" # what do when we can't encode to the desired output encoding # options are: # - 'strict' : raises a UnicodeEncodeError (default) # - 'replace': replaces missing characters with '?' # - 'ignore' : suppress missing characters on_error = "replace" db = MySQLdb.connect( "localhost","matan","pass","youtube", charset='utf8', use_unicode=True ) cursor = db.cursor() sql = "SELECT * FROM VIDEOS" try: cursor.execute(sql) for i, row in enumerate(cursor): try: # encode unicode data to the desired output encoding title = row[0].encode(encoding, on_error) link = row[1].encode(encoding, on_error) except UnicodeEncodeError as e: # only if on_error='strict' print >> sys.stderr, "failed to encode row #%s - %s" % (i, e) else: print "title=%s\nlink=%s\n\n" % (title, link)) finally: cursor.close() db.close() 

NB : you may also want to read this (specially the comments) http://drj11.wordpress.com/2007/05/14/python-how-is-sysstdoutencoding-chosen/ for more on Python, strings, unicode, encoding, sys.stdout and terminal issues.

Python — MySQL: Get column name or alias from query, answered Feb 23, 2013 at 19:23. James. 8,970 5 31 37. Add a comment. 29. Similar to @James answer, a more pythonic way can be: fields = [field_md [0] for field_md in cursor.description] result = [dict (zip (fields,row)) for row in cursor.fetchall ()] You can get a single column with list comprehension over the …

Fetching Million records from SQL server and saving to pandas dataframe

Have you tried using ‘chunksize’ option of pandas.read_sql? you can get all of that into a single dataframe and produce the csv.

If it takes more time then you can split each chunk into multiple files using the pandas.read_sql as a iterator and then after you did your work consolidate those files into a single one and submit it to postgres.

Php — how to fetch only a row from a mysql query, If you call mysql_fetch_array without a loop, You can specify the number of rows in the sql query using the ‘LIMIT’ syntax. Also, you can just remove the while loop and get the first row returned — if thats all you want. Consistency of a strange (choice-wise) set of reals

Pymysql cursor fetch multiple rows

EDITED : You can use fetchall method to get a static list and iterate through it.

sql = ('Select * from user;') cursor.execute(sql) data = cursor.fetchall() for i in range(0, len(data), 2): for row in data[i:i+2]: print(row["userID"],":", row["username"], ":", row["age"]) input() # to display two rows per enter key press 

Mysql — How to retrieve SQL result column value using, The MySQLdb module has a DictCursor: Use it like this (taken from Writing MySQL Scripts with Python DB-API ): cursor = conn.cursor (MySQLdb.cursors.DictCursor) cursor.execute («SELECT name, category FROM animal») result_set = cursor.fetchall () for row in result_set: print «%s, %s» % …

Источник

python-mysqlHow do I use Python to fetch an associative array from a MySQL database?

To fetch an associative array from a MySQL database using Python, you need to use the Python MySQL Connector library. Here is an example of code that you can use:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", passwd="yourpassword" ) mycursor = mydb.cursor(dictionary=True) mycursor.execute("SELECT * FROM customers") myresult = mycursor.fetchall() for x in myresult: print(x) 

The output of this code would be a list of associative arrays, each containing the data for a single customer from the customers table.

Code explanation

1. import mysql.connector : This imports the MySQL Connector library.
2. mydb = mysql.connector.connect(host=»localhost», user=»yourusername», passwd=»yourpassword») : This connects to the MySQL database using the specified username and password.
3. mycursor = mydb.cursor(dictionary=True) : This creates a cursor object with the dictionary flag set to True, which will return the results as an associative array.
4. mycursor.execute(«SELECT * FROM customers») : This executes the query on the database, which in this case is selecting all records from the customers table.
5. myresult = mycursor.fetchall() : This fetches all of the results from the query and stores them in the myresult variable.
6. for x in myresult: print(x) : This iterates through the myresult variable and prints out each result.

Here are some useful links for further reading:

More of Python Mysql

Источник