w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Getting brackets while executing query into mysql database using python
The result returned by cursor.fetchall() is a tuple of tuples containing the fetched data. Each tuple represents a fetched row. Because you are printing a tuple of tuples, you are seeing the braces. In order to display the data in a prefered format you'll have to iterate over the result and print each row accordingly. The example below would print each row on a new line, and will separate the columns with a vertical bar: for row in result: print " | ".join(row)

Categories : Python

Slow MySQL queries in Python but fast elsewhere
Tho you have only 501 rows it looks like you have over 50 columns. How much total data is being passed from MySQL to Python? 501 rows x 55 columns = 27,555 cells returned. If each cell averaged "only" 1K that would be close to 27MB of data returned. To get a sense of how much data mysql is pushing you can add this to your query: SHOW SESSION STATUS LIKE "bytes_sent" Is your server well-resourced? Is memory allocation well configured? My guess is that when you are using PHPMyAdmin you are getting paginated results. This masks the issue of MySQL returning more data than your server can handle (I don't use Navicat, not sure about how that returns results). Perhaps the Python process is memory-constrained and when faced with this large result set it has to out page out to disk to han

Categories : Python

Python MySQL queue: run code/queries in parallel, but commit separately
What probably happens is you share the MySQL connection between the two threads. Try creating a new MySQL connection inside each thread. For program 2, look at http://www.celeryproject.org/ :)

Categories : Python

Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
I am hoping for a better answer than the following. While some of these solutions might "fix" the problem, they don't answer the original question regarding what causes this error. Set PDO::ATTR_EMULATE_PREPARES=>true (I don't wish to do this) Set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (didn't work for me) Use PDOStatement::fetchAll() (not always desirable) Use $stmt->closeCursor() after each $stmt->fetch() (this mostly worked, however, I still had several cases where it didn't) Change PHP MySQL library from php-mysql to php-mysqlnd (probably what I will do if no better answer)

Categories : PHP

Is it possible to combine MySQL queries to multiple tables into a single query based on the results from one of the queries?
You want to look at MySQL Joins. I think this may do something like what you're after, but it will almost definitely need debugging! SELECT DISTINCT s.ownerid, s.message FROM statusupdates s LEFT JOIN friends f ON ($userid = f.requestfrom) LEFT JOIN friends f ON ($userid = f.requestto) ORDER BY s.createddate;

Categories : PHP

Executing many queries in one transaction
The "trick" with monads is to have each operation live as deeply inside of the computation as possible. For instance, let's say we have result :: IO (Either ParseError [(String, [SqlValue])]) then we can write liftM go result so that go only needs to process something of type Either ParseError [(String, [SqlValue])] and can ignore IO. We can go deeper. liftM (liftM makeQuery) result would be where we could put a makeQuery :: [(String, [SqlValue])] -> SqlQuery builder which creates the query we want to send to the database. This is a pure function that would be very easy to test on its own, say in a unit test where we build the [(String, [SqlValue])] bit by hand. We can move between layers by unwrapping. Each monadic layer has its own style of unwrapping. For instance, we can u

Categories : Haskell

Queries executing very slowly
I found out the reason. As I said before (in the comments) I did EXPLAINs for every subqueries and noticed some differences with the same EXPLAINs on the local machine (for 2 of 5 subqueries). It solved by creating additional indexes. Different machines - different results. I expected that it would be some differences, but I could not even did the EXPLAIN. That's a strange. Helped only the partial EXPALINs. Thanks for all.

Categories : Mysql

Python thread timer, not executing or executing instantly
You lost all the indentation in your code snippet, so it's hard to be sure what you did. The most obvious problem is responseTimer.start. That merely retrieves the start method of your responseTimer object. You need to call that method to start the timer; i.e., do responseTimer.start(). Then it will produce the output you expected, with a delay of about 2 seconds before the final "timeout!" is printed.

Categories : Python

Executing multiple insert queries
According to MySQL INSERT plan we have following issues with time to spend on: - Connecting - Sending query to server - Parsing query - Inserting row (1 × size of row) - Inserting indexes: (1 × number of indexes) - Closing Due to this, multiple insert is much more faster since it will produce Connecting, Sending query to server, Parsing query, Closing overheads only once. And this is shown in manual too: If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make

Categories : Mysql

Executing queries from sqlite file in iOS
Your .sqlite script is just a text file so you can load it. You can read it in line by line but you can also read the whole script (depending on size) into an NSString (stringWithContentsOfFile:encoding:error:) and then use the contents however you want.

Categories : IOS

Does it make sense to wrap PHP mysqli prepared statements for executing single queries?
To have such a function is actually the only sane way. While using raw API functions right in the application code, so much advertised on this blessed site of Stack Overflow, is one of the worst practices ever. And yes, it makes sense even for the single query execution. As the only prepared statement's purpose is to format your query properly and unconditionally. Though, to create such a function for mysqli using native prepared statements is a durn complex task. One need A LOT of experience and research to accomplish it. Say, only to add an arbitrary number of parameters to a query, you will need a screenful of code: Bind multiple parameters into mysqli query And you will need twice as that to get your results into array! However, for the emulated approach it would be much easier

Categories : PHP

Linking different schemas from different mysql servers into only one mysql server so he can manage the queries
MySQL Proxy: The MySQL Proxy is an application that communicates over the network using the MySQL network protocol and provides communication between one or more MySQL servers and one or more MySQL clients. However, note:  Warning MySQL Proxy is currently an Alpha release and should not be used within production environments. The FEDERATED Storage Engine: The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables. Replication: Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

Categories : Mysql

how to enable slow_query_log in mysql for log the slow executed queries in mysql
Depending on your version, the relevant options could be slow_query_log_file and slow-query-log (the log-slow-queries option has been deprecated since v5.1.29 and was removed in v5.6.1) Whatever your version, you also need to adjust the values of long_query_time and min_examined_row_limit Read also: 5.2.5. The Slow Query Log

Categories : Mysql

working in python console while executing a boost::python module
You have two options: start python with the -i flag, that will cause to drop it to the interactive interperter instead of exiting from the main thread start an interactive session manually: import code code.interact() The second option is particularily useful if you want to run the interactive session in it's own thread, as some libraries (like PyQt/PySide) don't like it when they arn't started from the main thread: from code import interact from threading import Thread Thread(target=interact, kwargs={'local': globals()}).start() ... # start some mainloop which will block the main thread Passing local=globals() to interact is necessary so that you have access to the scope of the module, otherwise the interpreter session would only have access to the content of the thread's scope.

Categories : Python

Is there an alternate to transactions that allow executing multiple concurrent queries to a table & making sure the table is in consistent state?
MySQL features table locks and locks (a kind of mutex). PHP supports pretty much all POSIX locking mechanisms, such as mutexes and semaphores (but these are not available by default, see the related "Installing/Configuring" manual chapters). But really, I see little reason why you would want to implement your own synchronisation mechanism: transactions exist for this purpose precisely, and are likely to be more efficient and reliable than anything home-brewed. (if performance is your concern, then a database back-end may not be the right choice)

Categories : PHP

MySQL Query is not Executing
As per usual, there is absolutely NO error handling, so you will never see that add is a reserved word and needs to be quoted with backticks: INSERT INTO listings (cat_id, comp, `add`, cit ^---^-- here Never ever assume a query is succeeding. Always check return values for failure, and be pleasantly surprised when things actually work.

Categories : PHP

Nodejs mysql query not executing
I hate to anwser my own question, but i found the solution. I miss red the documentation. Its possible by setting the option {multipleStatements: true} in your createConnection() function. So it is like: mysqlConnection = mysql.createConnection({ host: settings.mysqlServer, user: settings.mysqlUser, password: settings.mysqlPassword, multipleStatements: true }); mysqlConnection.query( 'CREATE DATABASE NodeTest;USE NodeTest;CREATE TABLE tblUsers (userId varchar(32), userName varchar(255), friendlyUserName varchar(255));' , function( oError ){ if ( oError ) throw oError; console.log( 'Database created.' ); });

Categories : Mysql

Executing sql command in a view in mysql
The view is as it states, only a view. The actual select command will execute on the underlying table(s) What needs to be noted is Updatable and Insertable Views Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following: and then a big list follows.

Categories : Mysql

MySQL Connector/Python - insert python variable to MySQL table
just had the same issue. Are you using numpy? One of your passed values could be of type numpy.float64 which is not recognized by the mysql connector. Cast it to a genuine python float on populating the dict. This worked for me. hth, gr~~~

Categories : Python

MySql #1243 error while executing it through phpMyAdmin
That is because the PMA doesn't use the same mysql session to execute the queries that's why you receive the error. But if you were to execute this in a script using the same mysql function it should work.

Categories : PHP

PHP, MYSQL Nested Queries
In this case, you have the same columns coming out from the query. Some are from Project and some are from Todo. The challenge is to order them in the right way: select which, name from ((select 'Project' as which, name, 1 as ordering, projectid, NULL as id from Project p ) union all (select 'List' as which, entry, 2 as ordering, projectid, id from Todo t ) ) t order by projectid, ordering, id;

Categories : PHP

How to optimize queries in mysql
When I am generating my queries with optional fields, I create an array of each field, then join them with implode $query_array = array(); $queries_stringed = ''; if(strlen($location) > 0){ $query_array[] = "location ='{$location}'"; }; if(strlen($minprice) > 0 && strlen($maxprice) > 0){ $query_array[] = "(price BETWEEN '$minprice' AND '$maxprice')"; }; if(!empty($query_array)){ $queries_stringed = "WHERE ".implode(" AND ", $query_array); }; $result = mysql_query("SELECT * FROM room_tb $queries_stringed");

Categories : PHP

mysql 3 queries on 2 tables in one
This is a query that requires a join and conditional aggregation: select j.jobid, j.description, j.someinfo, sum(case when il."type" = 'add' then amount else 0 end) as AmountAdd, sum(case when il."type" = 'rem' then amount else 0 end) as AmountRem, (sum(case when il."type" = 'add' then amount else 0 end) - sum(case when il."type" = 'rem' then amount else 0 end) ) as totaladdrem from tbl_jobs j left outer join tbl_invlog il on j.jobid = il.jobid group by j.jobid, j.description, j.someinfo; Note some things. First, the tables have table aliases, defined in the from clause. This allows you to say which table the columns come from. Second, the table aliases are always used for all columns in the query. MySQL would allow you to just do group

Categories : Mysql

How can i join this 2 MySQL queries together
In MS-SQL it's possible to select this as: SELECT p.pid, p.link, p.created_at, (SELECT AVG(rating) FROM ratings r where r.pid = p.pid) as rating FROM products p ORDER BY timestamp DESC

Categories : PHP

MySQL Joins and Queries
SELECT name,clientid FROM clients a JOIN maintenancewindow b ON a.clientid=b.clientid JOIN locations c ON b.maintenanceid=c.maintenanceid I`m assuming that is the result you want The end goal is to get a list of my clients name, location, and maintenance windows in readable format, ie the names of clients, locations, maintenance windows versus the 'id's'. This part is confusing.You mean a specific id?Just add WHERE id=someid.

Categories : Mysql

How to refactor these MySQL queries with PHP?
If you want one page which shows all sites, you can do the following: add "?site=CHI001" to the end of your link. In your script, this will show up as $_GET['site'] (you can use any name you like). Convert the variable with mysql_real_escape_string - `$site = mysql_real_escape_string($_GET['site']);' DO NOT! attempt to put the variable in directly - this WILL let an attacker run any database command they like! See php.net article on SQL injection or take my word for it - it is BAD! Now you can add this value into your sql - replace WHERE site = 'CHI001' with WHERE site = '$site' Now you can test the page, for different sites.

Categories : PHP

PDO much slower than MySQL queries
The most PDO way to achieve the same (also taking advantage of UNION to aggregate the results within the database layer): public function numJobsMil() { $STH = $this->_pdo->dbh->prepare(' SELECT job_id FROM job_materials_lot WHERE lot = ? UNION ALL SELECT job_id FROM job_materials_prop WHERE lot = ? '); $STH->execute([$this->_lot, $this->_lot]); return $STH->fetchAll(PDO::FETCH_COLUMN, 0); } Whenever I see UNION used in this way, I cannot help but wonder whether the two tables ought to be combined, with a column to flag from which table the underlying records originated.

Categories : PHP

Combining MySQL queries
You can use subquery. You can combine both queries into something like: $search_query= "SELECT * FROM users WHERE fname LIKE '%".$fname."%' AND lname LIKE '%".$lname."%'" AND id in (SELECT id FROM relations where user1= '".$user_id."' AND status= 1)"

Categories : PHP

mysql different select queries in one
Shouldn't you group by post_ID ? (that would return only one line) SELECT comments.* , COUNT(approved.comment_approved) AS "has_commented" FROM wp_comments AS comments JOIN wp_comments AS approved ON (comments.comment_author = approved.comment_author) WHERE comments.comment_post_ID = 14616 GROUP BY comments.comment_post_ID ; Or do you want one line per "approved" comment ?

Categories : Mysql

Mysql queries don't seem to be working
You're not actually issuing a query in your code: <?php include '../inc/connect.php'; if(isset($_POST['hide'])){ $chk = (array) $_POST['hide']; $p = implode(',',array_keys($chk)); echo $p; $t = "SELECT * FROM headerrotatorimage WHERE id IN ($p)"; echo $t; // Execute query and process result set for $t $s = "UPDATE headerrotatorimage SET status = 2 WHERE id IN ($p)"; echo $s; // Execute query and process result set for $s } ?>

Categories : PHP

Excel VBA program stopped executing MySQL query
Alright, seems compatibility issues was creating the problem. I was using Microsoft professional plus 2010. I re-installed the office and followed most of the steps mentioned in the below link. http://www.jkp-ads.com/Articles/StartupProblems.asp. As a final resolution, I did the following steps. Opened C:Program Files (x86)Microsoft OfficeOffice14. Right click on Excel.exe and select Properties Select compatibility and unchecked the option "Run this program in compatibility mode for".

Categories : Mysql

Ignore mysql error messages when executing an sql file
As documented under INSERT Syntax: The INSERT statement supports the following modifiers: [ deletia ] If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

Categories : Mysql

reactivating a query after executing an insert statement in PHP/MYSQL
Well you have to implement any opportunity to identify the user. Maybe a user system, maybe only a cookie/localstorage or something else. Important is, that the user can by identified with an unique id. In the next step you create a mysql table where you save the first selection of the user may be in this format id | user_id | department ------------------------- 1 | 3 | depA 2 | 15 | depB Then when the user visits your page again, you identifie the user and populate the select element.

Categories : PHP

Error on executing proc in mysql insert statement
Your below line have error: SET @sql_text = concat('insert into simsed_',@table_name,' (class) values ',@new_supply) ; Change it with the following code: SET @sql_text = concat('insert into simsed_',@table_name,' (class) values ("',@new_supply,'")') ;

Categories : Mysql

Using javascript Variables in MYSQL Queries
Based on user2246674's constructive comments, I also learned something today. Rather than this: client.query("INSERT INTO archive (attendance) VALUES (" + att + ")" Try this instead: var att = result[0].curattend; client.query("INSERT INTO archive (attendance) VALUES (?);", [att], function(err,info){ }); // This creates the insert statement INSERT INTO archive (attendance) VALUES (att);

Categories : Javascript

how to run MySQL sub queries to count the records.
From what I understand by your question, you need something like below SELECT a.id, count(distinct b.id) Total_GroupB, count(distinct c.id) Total_GroupC from GroupA a LEFT JOIN GroupB b ON a.id=b.groupa_id LEFT JOIN Groupc c ON b.id=c.groupb_id GROUP BY a.id

Categories : Mysql

What is the reason the counts are different in the mysql queries?
Something like this is happening: Persons table ID Name -- ------------ 1 John Doe 2 Xaisoft Colors table PersonID ColorName -------- --------- 1 Red 1 Yellow 2 Yellow Now query Persons.* for John Doe while joining to Colors: SELECT p.* FROM Persons p INNER JOIN Colors C ON c.PersonID = p.ID WHERE p.Name = 'John Doe'; Your result will be one p.* row for each color under John Doe: ID Name -- ------------ 1 John Doe 1 John Doe If you apply a COUNT DISTINCT against this you'll end up with 1. If you count Red and Yellow separately, you'll get one for each query. Add them up and you'll get 2. Look at it this way: if you throw out the COUNT and just SELECT DISTINCT Persons.* you get these results: Red or Yellow for John Doe: SELECT DI

Categories : Mysql

Error in combining two queries in mysql
You have to join these on some column like this: SELECT A.*, B.* FROM (SELECT COUNT(*), books_authors.book_id FROM (books_authors WHERE books_authors.author_id IN (SELECT author_id FROM obl_authors WHERE nationality='Indian' ) GROUP BY books_authors.book_id) A INNER JOIN (SELECT COUNT(*), books_authors.book_id FROM books_authors GROUP BY books_authors.book_id ) B ON A.book_id = B.book_id To check if it is equal in count you actually do not need to join the tables, because you have just plain number, so you do like this: SELECT (SELECT COUNT(*), books_authors.book_id FROM (books_authors WHERE books_authors.author_id IN (SELECT author_id FROM obl_authors WHERE nationality='Indian' )) - (SELECT COUNT(*), books_authors.book_id FROM books_authors)

Categories : Mysql

Mysql: Set time_zone for select queries, UTC for everything else
User CONVERT_TZ() in mysql function SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); Ref : http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

Categories : Mysql

Complex issue with MySQL queries
In simplest terms, if restructuring is undesirable, then I would do a simple query to determine the value of orders, returns, and sales seperately and then join those together. This could be done using UNION and subqueries as in the following example : SQLFiddle I've also taken the liberty of swapping the FLOATs for DECIMALs. There is probably room for improvement on indexes and the like but this should put you on a good track for determining the sums. If you look at the subquery you'll see that ORDER and RETURN selects are selecting a negative value as per your requirement. One potential pitfall is that any records for which the record from Product has been deleted would not be included. This could be avoided by changing the Product joins into LEFT JOINs and handling the NULL value

Categories : Mysql



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.