Python mysql select array

Select Array from MySQL in Python without For Loop-mysql

Python is not my forte and I’ve not used executemany before, but I don’t think it’s supposed to be used for executing code that’s supposed to return something. You probably want to use IN with your query.

query = "SELECT fips FROM us WHERE zip IN ('%s')" % "','".join(zipcode) cursor.execute(query) results = cursor.fetchall() 

More questions

• PHP: How to select single value from single row in MySQL without using array for results
• MySQL select all rows from last month until (now() — 1 month), for comparative purposes
• MySQL — Select from a list of numbers those without a counterpart in the id field of a table
• SELECT * FROM MySQL Linked Server using SQL Server without OpenQuery
• Get data from multiple SELECT sub-queries for reporting from MySQL database
• Accessing MySQL from Python 3: Access denied for user
• MySql Query: Select top 3 rows from table for each category
• Flask and Python how to make search engine for data from mysql database
• Constructing mysql select from $_POST array
• SELECT in a while loop in python with mysql
• Create PHP multidimension associative array from MySQL linked tables SELECT query
• How to insert values in mysql from loop in Python
• MYSQL — Select specific value from a fetched array
• Select multiple rows from MySQL tables for 1 user
• How to select JSON object from JSON array field of mysql by some condition
• Correct way for Codeigniter AJAX get data from MySQL database without refreshing
• MySQL store procedure: How to declare a Cursor for a Select from a temporary table?
• i need to get data from mysql DB and put it to a json array and use the json array for textbox auto complete
• Create one div for each row of array fetched from MySQL
• How to loop through an array return from the Query of Mysql
• MySQL + PHP: Select all results from one table for each result of another
• Select items that may be related (like: for ‘orange’, give ‘bread’) from MySQL database using PHP
• Multiple mysql queries from within for loop
• list of array assigned to be used in FOR loop mysql query php
• What is the process for using MySQL from Python in Windows?
• Declaring and filling an array in Python from fetching MySql array
• MySQL transactions not stopping race conditions from for loop
• PHP while loop omits first entry from MySQL database select
• SELECT MAX from one column with SUM and GROUP BY for another, without subqueries
• (flask) python — mysql — using where clause in a select query with variable from URL

More questions with similar tag

• How can I find all records for a model without doing a long list of «OR» conditions?
• 2 exactly same mysql queries give 2 differents ‘explain’ output : why?
• Installation of MySQL-python in shared hosting
• How to make HTML dropdown list with values from database
• Transaction MySQL
• Trouble installing mysqlclient via pip
• Add Application Name / Program Name in mysql connection string
• Sakila Schema is not Imported to MySQL
• inserting all entries stored in an array (for statement)
• Comma Separated Multiple Autocomplete in one field
• Show duplicate row in single row mysql
• Does MariaDB support lower_case_table_names=0 in windows?
• Reducing query with multiple “OR” statements in php
• Connect website to MySQL database in cPanel
• Cannot insert datetime with mysql command line
• Adding multiple items to a model in catalyst
• Getting last insert id in Excel ADODB connection to MySQL database
• yii CActiveDataProvider Multiple Table Join
• Mysql how to stop rounding off decimals
• Warning: mysqli_fetch_array() expects parameter 2 to be long, object given in
• Could not load file or assembly ‘MySql.Data’ or one of its dependencies
• SQL : Multiple left joins with similar tables
• ST_Area() for latitude and longitude
• URL and mod_rewrite: use many special chars and keep data safe from attacks
• I thought curtime(), now() and current_timestamp are valid default datetime values in MySql?
• Failed to bootstrap Galera Cluster
• How to copy a database using HeidiSQL?
• When querying large data sets, prevent script timeout
• htmlspecialchars() — How and when to use and avoid multiple use
• Cannot connect to MySQL database running on AWS

Источник

Convert A SQL Query Result To A List In Python | MySQL

When dealing with database queries we use the for-loop to access each record and deal with it one at a time. In some cases it’s better to deal with the database results in a variable so we do not need to query again. The Python List object allows us to store a SQL query result easily as it allows for duplicates and permits changes too.

This post is a follow-up to the “Write a List to CSV file” article. In this case we are querying the database and loading the results into some Python objects like a List, Tuple and Dictionary.

Note: We’re going to skip the virtual environment.. (MySQL Ex. Here) ..but it is recommended to use a virtual environment for all your projects.

Python Environment Connect to MySQL

You should be familiar with creating and connecting to a MySQL database. Here is some quick sample code to install in a new Python environment.

1. activate the environment

.\myenv\Scripts\activate (windows) source /myenv/bin/activate (linux)

2. install the mysql driver

pip install mysql-connector-python

Use the following example to connect to a localhost MySQL database.

import mysql.connector conn = mysql.connector.connect( host="localhost", database="somedbname", user="myuser", password="goodpasswd" )

What is a Python List []

The List is similar to an Array in other languages and it is indexed starting 0. The List can contain strings, numbers and objects too, here are some simple string examples.

string = [] ## New empty List string1 = ['a', 'b', 'c', 'd', 'e', 'f', 'g'] string2 = ['Brazil', 'Panama', 'Canada', 'India', 'Decentraland'] number = [2,3.6,7,-23,99] ## can contain decimal and negative numbers together = string1[0:2] + string2[2:5] + number[2:4] ## concatenate lists print(string1[3]) ## get just 1 value print(string2[4]) print(string1[2:5]) ## Range: get values from index 2 -> 5 print(number[1:5]) print(together) Values from all 3 in 1 list string1[:] = [] ## clear the list

Append to List []

newlist = ['Houston'] newlist.append('Decentraland') ## append just 1 string newlist.append(string2[2]) ## append the 3rd value from another list newlist.append(number) ## append the entire number list print(newlist)

MySQL Table and Query

For this sample I created a table called “myusers” which includes the users first name, last name and the city they work in. In this example we are using a cursor and then querying this table for the (db tales com) first record and then all of them. Keep in mind that Python is returning a Tuple () for one record but then a List with many Tuples that contains our data, we are also querying 3 fields from the database.

cursor = conn.cursor() cursor.execute("SELECT fname, lname, city FROM myusers") onerecord = cursor.fetchone() results = cursor.fetchall() print(onerecord) ## returns as a Tuple print(results) ## returns as a List of Tuples

Источник